Date & Time difference

[chrisellis250]

Hi all,

I am looking at a formula to show the difference in time (HH:MM:SS) between two times and dates.

In Column B I have the date in the following format dd.mm.yy

In Column C I have the start time in the following format hh:mm:ss

In Column P I have the end time in the following format hh:mm:ss


Ultimately I am looking at the difference between the end time in the 1st row and the start time in the next row, but the formula I used (=B2+P2-B3-C3) shows ####### in some of the cells.

Any ideas please?

[6StringJazzer]

Your formula looks OK. Is your date a true Excel date formatted as dd.mm.yy or is it a text value?

When you get a ##### result it is because you have caculated a negatve date/time. Excel does not believe in negative date/times. Check your data to see if that is the case.

[Roel Jongman]

please post an example file. Most likely you are getting negative outcome. but when dates are included that should not happen. Are you sure that the endtimes are on row two and the begin time is on row 3?

otherwise I suggest using (=B3+P3-B2-C2) that seems to make more sense to me. But to be sure and to get better help attach an example file.. see yellowbanner at the top for instructions how to..

[chrisellis250]

Yes, its an issue with negative times. I can display it if I change excel to 1904 formula, but then that causes an issue with my dates.

[6StringJazzer]

One workaround is

Please Login or Register  to view this content.