Software

Ask something
Discussion Locked

Excel date & time frustration

0 helpful
10 replies

by chainsawz

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I am working in Excel 97, and trying to calculate the difference between two dates and times. I have ‘time in’ in cells A1:A2 – ‘time out’ in cells B1:B2. I calculate the difference between the in and out dates in cell C1:C2 -- =B1-A2 formatted as d:h:mm. This does not calculate the days correctly unless the dates are in the same month (see below) Also, when I try to sum the differences: =sum(C1:C2) (26:1:53+22:22:32), I get the crazy answer of 20:0:25. Whatam I missing? How can get the difference between the time in and the time out in the d:h:mm format to display correctly, and then how can I sum those differences??

Time In
09/19/01 9:21 AM
09/19/01 11:40 AM

Time Out
11/15/01 11:14 AM
11/12/01 10:12 AM

Difference (format d:h:mm)
26:1:53
22:22:32

10 replies

by chainsawz

I needed the result to be in the d:h:mm format, and this method would only display as 111.02, so it didn't work for me. I am grateful for the try tho!

by kees.valkenswaard

Ok. Although he had many goodies, do not trust everything from Cpearson for a start. The function DATEDIF is very dangerous as it was probably a mistake. With leap years there may be a difference of zero days between 29 February and 1 March.
Also realize that d in date formats gives you either the day of the month or the day of the week, but not a count of days.

What you want is something very special. It can be done.

I will use the international notation and not the local variation theyuse in some corners of the world. So you may have to replace my list separator ; [semi colon] by , [comma]

Put in C1 the following formula:
=TRUNC(B1-A1) & TEXT(B1-A1;":hh:MM")
Just propagate the formula with the fill handle downwards.

Now the great trick for the sum. Suppose you have these values in cells A1 through B10.
Put the following formula in C11:
{=TRUNC(SUM(B1:B10-A1:A10)) & TEXT(SUM(B1:B10-A1:A10);":hh:mm")}

NOTE1: enter the formula without the {} and use SHIFT-CTRL-ENTER to finish the array function.
NOTE2: It may be necessary to right align the cells in column C.
NOTE3: you can not do any calculations with the content of cell C11. Just make the normal sum for all B cells and subtract the sum of all A cells if you want to do calculations.
Good luck.

by kees.valkenswaard

You probably noted already that in the first formula it should be mm rather than MM. It would still work with MM but the convention would be to use mm.

by kees.valkenswaard

It is obvious that range names could make your functions much easier to write and maintain. You just need a range name for the A cells and one for the B cells.

by kees.valkenswaard

NOTE4: in an international environment functions like TEXT and CELL are a real disaster, as they are purely local.
In that case use instead
=TRUNC(B1-A1) & ":" & HOUR(B1-A1) & ":" & MINUTE(B1-A1)

by kees.valkenswaard

If you want to do it really nice you may have to check in an IF function whether HOUR and MINUTE would yield one or two digits and add an extra zero in order to get always two digits. That zero would be text!

by kees.valkenswaard

OK, I received an e-mail from anther user. The formula for the sum would be:

{=TRUNC(SUM(B1:B10-A1:A10)) & IF(HOUR(SUM(B1:B10-A1:A10))<10;":0";":") & HOUR(SUM(B1:B10-A1:A10)) & IF(MINUTE(SUM(B1:B10-A1:A10))<10;":0";":") & MINUTE(SUM(B1:B10-A1:A10))}
Again ; [semi colon] may have to be replaced by , [comma]

by chainsawz

This worked like a charm!! As you said, I needed to replace the semi colons with commas, and then the formulas worked and displayed the results in the d:h:mm format. I really do appreciate your help!

This discussion is closed to new replies.