Calculate Day Difference From 2 Dates

[ssweet]

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

[RichardSchollar]

Hi

Something wrong with just:

=B1-A1

if end is in B1 and start in A1?

Richard

[ssweet]

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?

[RichardSchollar]

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

[ssweet]

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.

[shg]

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:

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

[Dave Hawley]

Do you mean

---

Try this formula. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas

	=Day(B1)-Day(A1)

---

[ssweet]

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

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

cheers