MrExcel.com - Your One Stop for Excel Tips & Solutions

Excel time formula

Posted by Roland Johnson on March 31, 2001 6:00 AM

Looking for a formula to subtract one time from another and convert that product to decimalized time.
i.e.
start 7:00AM
end 5:15PM
Total 10:15 I need to convert that to 10.25

Appreciate in advance any help.

Posted by Aladin Akyurek on March 31, 2001 6:45 AM

=============

=((HOUR(A2)+MINUTE(A2)/60)+(A2 LessThan A1)-(HOUR(A1)+MINUTE(A1)/60))

Replace LessThan with the appropriate relational symbol and the cell of this formula as General.

Aladin

Posted by Dave Hawley on March 31, 2001 7:07 AM

Hi Roland

With 7:00AM in cell A1
and 5:15PM in cell B1

Try: =(B1-A1)*24

Format cell as General.


Dave

OzGrid Business Applications

Posted by Dave Hawley on March 31, 2001 7:13 AM


Forgot to say, if you wont know which cell will have the greater time then you could use:

=(MAX(A1:B1)-MIN(A1:B1))*24


Dave


OzGrid Business Applications

Posted by Aladin Akyurek on March 31, 2001 9:06 AM

: Looking for a formula to subtract one time from another and convert that product to decimalized time. : i.e. : start 7:00AM : end 5:15PM : Total 10:15 I need to convert that to 10.25


No need for that. A2 LessThan A1 is a logical expression that evaluates either to 1 or to 0.


Posted by Dave Hawley on March 31, 2001 6:47 PM

LOL!


>>No need for that. A2 LessThan A1 is a logical expression that evaluates either to 1 or to 0.


Thanks Aladin, I realise you and Mark dream of Boolean logic :o)


So there's a need for:
=((HOUR(A2)+MINUTE(A2)/60)+(A2 LessThan A1)-(HOUR(A1)+MINUTE(A1)/60))

But Not
=(MAX(A1:B1)-MIN(A1:B1))*24

You guys crack me up.

Dave

OzGrid Business Applications

Posted by Aladin Akyurek on March 31, 2001 11:22 PM

Re: LOL!

Thanks Aladin, I realise you and Mark dream of Boolean logic :o) So there's a need for: =((HOUR(A2)+MINUTE(A2)/60)+(A2 LessThan A1)-(HOUR(A1)+MINUTE(A1)/60)) =(MAX(A1:B1)-MIN(A1:B1))*24

I'd urge Roland to use the shortest formula that has been suggested.

I'd still prefer +A2 LT A1 over (MAX(A1:B1)-MIN(A1:B1)) when both apply.

Aladin

Posted by Dave Hawley on April 01, 2001 12:49 AM

Re: LOL!

>>No need for that. A2 LessThan A1 is a logical expression that evaluates either to 1 or to 0. Thanks Aladin, I realise you and Mark dream of Boolean logic :o) So there's a need for: =((HOUR(A2)+MINUTE(A2)/60)+(A2 LessThan A1)-(HOUR(A1)+MINUTE(A1)/60)) But Not =(MAX(A1:B1)-MIN(A1:B1))*24 You guys crack me up.

>I'd urge Roland to use the shortest formula that has been suggested.


So that would be my first sugestion
=(B1-A1)*24

and not your:
=((HOUR(A2)+MINUTE(A2)/60)+(A2 LessThan A1)-(HOUR(A1)+MINUTE(A1)/60))

ROFL


OzGrid Business Applications

Posted by Barrett (engalnd) on May 14, 2001 11:09 AM

Re: LOL! london calling

LONDONs saying im with OzGrid (Dave) any day! >>No need for that. A2 LessThan A1 is a logical expression that evaluates either to 1 or to 0. Thanks Aladin, I realise you and Mark dream of Boolean logic :o) So there's a need for: =((HOUR(A2)+MINUTE(A2)/60)+(A2 LessThan A1)-(HOUR(A1)+MINUTE(A1)/60)) But Not =(MAX(A1:B1)-MIN(A1:B1))*24 You guys crack me up.