 # Calculate Day Difference From 2 Dates

• Hi Guys is anyone aware of a formula/macro that can calculate (in days, hours and minutes) how much time has elapsed between two date and time stamps?

For example [30/07/2008 00:30] and [01/08/2008 01:45], so in days hours and minutes it would look somewhat like this: 02:01:15

Cheers

• Re: Calculating Time Elapsed Between Two Date And Time Stamps

Hi

Something wrong with just:

=B1-A1

if end is in B1 and start in A1?

Richard

• Re: Calculating Time Elapsed Between Two Date And Time Stamps

sorry i thought it would be that easy too...

however probably my example wasnt the best. That formula is ok if the dates are within a few days of each other, but if they are over a month apart, it wont calculate correctly and we need to calculate in days for our reporting. so we will need to calculate:

[30/07/2008 12:30:00 AM] and [28/09/2008 1:45:00 AM] The formula will only count up to 30 days and we require it to go above that. Is that possible?

• Re: Calculating Time Elapsed Between Two Date And Time Stamps

I don't understand I'm afraid - the formula will do for any 2 date/time values used in Excel.

The formatting of the result is another issue altogether

• Re: Calculating Time Elapsed Between Two Date And Time Stamps

oh. sorry it may be a formatting issue.... if so then i am not sure how to make it count in just days not months

[30/07/2008 12:30:00 AM] [1/09/2008 12:30:00 AM] with the above formula would be: 02:00:00 as the day value will only go up to 31

for that example i need it to say: 33:00:00.

• Re: Calculating Time Elapsed Between Two Date And Time Stamps

As you've learned, Excel considers d or dd to be the day of the month, and so won't exceed 31. You can do this, but the result will be text:

[COLOR="Blue"]=INT(B1-A1) & TEXT(B1-A1, ":hh:mm")[/COLOR]

[SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

• Re: Calculating Time Elapsed Between Two Date And Time Stamps

Do you mean

[frc]=Day(B1)-Day(A1)[/frc]

• Re: Calculate Day Difference From 2 Dates

=INT(B1-A1) & TEXT(B1-A1, ":hh:mm")

the result worked perfectly. thank you to everyone else for their help too!!

cheers

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!