Calculating time differences in Excel
Posted by Jon on May 01, 2001 5:27 PM
I have a user whose spreadsheet has 3 columns that contain time values.
Column A = start time
Column B = finish time
Column C = duration (difference in time between A and B)
Problem is that they have keyed in all the times already in a regular format, for example, 12:45pm looks like "1245". If I try to format the existing cells into Time format the value of the cells come out to be like Jan 01, 1901 and so forth with a time of 12:00am. Is there a way to convert all the existing times that look like this "hhmm" to look like this "hh:mm"?
I tried to look in Help, but I get confused with the decimal talk and using 1440, etc. Any help will be appreciated, thanks.
Posted by Kevin James on May 01, 2001 6:41 PM
Hi Jon,
As an example, given that the time cell is in columns A and B, put this formul in E1 then copy it down to the same number of records. Then copy it across to column F. You'll have your formula.
=TEXT(TIME(LEFT(A1,2), RIGHT(A1,2), 0), "h:mm:ss AM/PM")
I found it in Help, under Time. I pasted and copied the formula and then altered the syntax to look at the incorrect time formatted cell.
Posted by Kevin James on May 01, 2001 7:04 PM
Read this first, then the next
Whoops, I forgot to take into account the possibility of 3-digit times (e.g. 103 AM).
I also removed the "seconds" from the formula.
=TEXT(TIME(IF(LEN(A5)<4,LEFT(A5,1),LEFT(A5,2)), RIGHT(A5,2),), "h:mm AM/PM")
See my first note for the rest of my response.
Kevin
Posted by Mark W. on May 02, 2001 9:54 AM
For a text representation of a 24-hour time
value (e.g., 1330) in A1 use:
=LEFT(A1,LEN(A1)-2)/24+RIGHT(A1,2)/1440
...and apply a Time format.
Posted by Kevin James on May 02, 2001 7:38 PM
Now why didn't I think of that?
My hat is off to you.
