PDA

View Full Version : Sleeper: Date Dif function



mattster1010
06-23-2008, 06:48 AM
Good Afternoon,

I have three columns, A,B & C, I have used the following formula to calculate the difference in hours and minutes bewteen the two dates listed in columns A and B (total is shown in C)


=TEXT(B2-A2,"h:mm")

this returns the example below:
A B C
16-06-2008 10:32:00.016-06-2008 11:22:00.00:50


I what to create a formula that will return a 'YES' for a value less than 24 hours and a 'NO' for a value greater than 24 hours. This formula will be based on the total of column C.

Can any one help / point me in the right direction please.

Cheers,

Mattster

Bob Phillips
06-23-2008, 06:56 AM
=IF(INT(B2-A2)>=1,"NO","YES")

mattster1010
06-23-2008, 07:09 AM
Thanks for the formula....

My total in column C is calculated in h:mm, how can I adjust your formula to calculate against this, instead of 1? I have added the spreasheet as an attachment to help expain.

Cheers,

Mattster

Bob Phillips
06-23-2008, 07:12 AM
No it isn't, you are outputting text that LOOKS LIKE hh:mm.

marshybid
06-23-2008, 07:15 AM
Thanks for the formula....

My total in column C is calculated in h:mm, how can I adjust your formula to calculate against this, instead of 1? I have added the spreasheet as an attachment to help expain.

Cheers,

Mattster
My understanding is that you will need to amend your existing formula from =TEXT(B2-A2,"h:mm") to;


=TEXT(B2-A2,"d:h:mm")

This is what I used in my sheet and it works fine. the h:mm format only counts in 24 hour format i.e.00 - 23

xld, I spent 20 minutes pulling together a formula, came up with exactly the formula you provided, only to find, of-course, that you had well and truly beaten me to it.....

Note to self, work faster

Marshybid