I want to enter the time without colons as hhmmss and calculate the time differences with another cell. For example I enter the beginning time in one cell and the ending time in another. Then I need to calculate the time difference. But it has to include hours, minutes and seconds.
The complication is that I want to enter the times without a colon but see it with the colon in the cell. To do this I formatted the cells with custom number format 00\:00\:00.
3 Answers
One option would be to use a combination of cell formatting with the Excel TIME functions.
Format your time input cells (A2 and B2 in my example) as Text. The format expected will always be hhmmss, so enter the leading zero for times with single-digit hours. Then you can calculate with this formula:
=TIME(LEFT(B2,2), MID(B2,3,2), RIGHT(B2,2))-TIME(LEFT(A2,2), MID(A2,3,2), RIGHT(A2,2))This grabs the left-most 2 characters as the 'Hours', the middle 2 characters as the 'Minutes', and the right-most 2 characters as the 'Seconds' and converts them to what Excel recognizes as a time. It then subtracts one from the other, and displays the result, with the formatting hhmmss:
EDIT: Seeing as the requirement isn't quite as specified in the question, I've amended the formula to take account of leading zeros by padding it out:
=TIME(LEFT(RIGHT("000000"&B2,6),2), MID(RIGHT("000000"&B2,6),3,2), RIGHT(RIGHT("000000"&B2,6),2))-TIME(LEFT(RIGHT("000000"&A2,6),2), MID(RIGHT("000000"&A2,6),3,2), RIGHT(RIGHT("000000"&A2,6),2))Horribly less readable, but now pads the value with zeros and uses the right-most 6, so should work regardless of how many zeros you use.
I believe you'll actually want to special format the result as hh:mm:ss in this case.
Here's another way to handle decoding your times the way you want to enter them:
I left the start and end times as unformatted numbers to make the action more visible. In this example, your start time is 25 seconds after midnight or noon, which you would enter as 000025. As a number, it will be stored as 25, even though your formatting will make it look like 00:00:25.
This approach peels off hours and minutes based on their position in the number being powers of 100. Seconds are always the right two digits, regardless. The formula in C2 is:
= TIME(INT(B2/10000), INT(MOD(B2/10000,1)*100), RIGHT(B2,2)) -TIME(INT(A2/10000), INT(MOD(A2/10000,1)*100), RIGHT(A2,2))The MOD function is the opposite of the INT function. It gives you the remainder after division.
I know this is an old post, but if (as I did) people are searching for a solution through countless forums to saving time entering in timesheets and not having to manually enter a "Colon", but having the colon visible, and then being able to carry out calculations..... all without using VBA Script, hidden cells and formulas....etc.... etc.... This is the solution I came up with - and it seems to work with leading zeros, 24 hour time, and timesheets spanning midnight (although would need adjustment if you expect a timesheet to span multiple midnights - i didn't see this as necessary in my case).
Also, I have no need for "Seconds", but you should be able to expand it easily enough if you follow the concept:
Custom Format your start time and end time cells as:
0#":"##
This turns them into "Text cells", but pads out digits to always show four digits (even with a leading nought, or zero for American friends).
Note, this will allow entry of ANY four digit number to look like a time format. So theoretically possible to enter in 0768, and it become the time 07:68, which of course doesn't exist. So either
a) create a data validation list as follows: - on a separate worksheet (tab), in a single column, create a list of all the acceptable values. In my case this was from 0500, 0515, 0530, 0545.....0445 as I was only dealing with 15 minute increments. If you need to drill down to seconds (seriously?) this would get quite long.....ie. 050001, 050002.... etc. Or you might write another formula just to create this list, and then copy the text data (paste values only) across into a new column to remove the formulas (I would). - Select all these cells of "acceptable values) and in the "Forumulas" tab of the toolbar select "Define Name". Give it a name like "MonkeyTimeList". - Back to your time sheet worksheet, select all the cells covering both start and end times, and go to the "Data" tab of the toolbar, select "Data Validation>Data Validation>Settings", in the "Allow" section select "List" from the drop down, the in the "Source" section type in the name you defined preceeded by "=", so in my case "=MonkeyTimeList". Becore you click "OK", select the "Error Alert" tab, tick the box "Show error alert...", and in the "Style" drop down select "Stop". Feel free to add an error message like "Monkey Man says you are an idiot, try and enter times in the CORRECT FORMAT!". Then click "OK". -This ensures that only data from your "acceptable" list can be entered as a time.
b) theoretically you could use conditional formatting just to highlight a cell where incorrect data is added. This would avoid using a separate worksheet for your data validation data. I haven't done it this way, which is why I say theoretically. That may be a different tutorial....
So, now you can enter your times in, without having to look for the colon and shift key each time.
If you REALLY want seconds in your cell format, then the custom cell format would look like:
0#":"##":"##
SO...... NOW TO CALCULATE HOURS from start time to finish time.... again, this is based on minutes only.... but if you can follow the logic, can be expanded to seconds too.
In the third column (assuming column A is Start Time, B is End Time, and C is Total Hours), enter the formula:
=IF(VALUE(B2)>=VALUE(A2),(SUM(VALUE(MID(TEXT(B2,"0000"),1,2)),VALUE(MID(TEXT(B2,"0000"),3,2))/60))-(SUM(VALUE(MID(TEXT(A2,"0000"),1,2)),VALUE(MID(TEXT(A2,"0000"),3,2))/60)),(SUM(VALUE(MID(TEXT(B2,"0000"),1,2)),24,VALUE(MID(TEXT(B2,"0000"),3,2))/60))-(SUM(VALUE(MID(TEXT(A2,"0000"),1,2)),VALUE(MID(TEXT(A2,"0000"),3,2))/60)))
Now to explain it...
The "IF" function distinguishes finish times on a 24 hour clock which are before midnight, and those which are after midnight. IF after midnight, 24 hours are added to the finish time to allow the calculation of Finish time minus Start Time to still work. IF before midnight, there is no need to add 24 hours. So for the purposes of calculation, the time 0100 is actually treated as 2500, 0200 is 2600 and so on. NOTE. this assumes that a shift runs no longer than 23hrs and 45min. If it does run longer, maybe you would need to factor in date columns to your formula.... again, I didn't need it.
The MID functions work on TEXT (hence the TEXT function) and returns the digits in specific locations from a referenced cell, in the specified format.... Soooo, "MID(TEXT(B2,"0000"),1,2)" looks at cell B2, and always looks at it in the format of 4 digits/letters, and returns the two digits starting at position "1". In my case this represents whole hours. Another example: "MID(TEXT(A2,"000000"),5,2)" would look at cell A2, and return the two digits starting as position "5", which could be whole seconds.
I placed the "VALUE" function in front of all this to turn the text returned back into a number, so that we can work with it in calculations.
So, this allows us for each cell to treat the hours, the minutes and the second separately. Calculations with hours are simple as they are whole numbers already. However Minutes are actually fractions of hours, or fractions of 60 minutes, so once you extract the digits representing "minutes", divide by "60" to give you a "fraction" of an hour. Like wise with seconds, divide by 3600 (number of seconds in an hour) to give you seconds as a "fraction of an hour".
Now that the times are represented as numbers with fractions, you can add or subtract as you like. In the case of my formula, will give the total number of hours between start time and finish time. I format the cell with the formula as Custom> 00.00 which then allows me to multiply by hourly rates etc.
If someone still really wants seconds in their formula. and get stuck, let me know. If I'm bored at work I may be able to assist.... but seconds??? really???
0
