Archive of Mr Excel Message Board


Back to Dates in Excel archive index
Back to archive home



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.


Re: Excel time formula

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


Re: Excel time formula

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


Re: Excel time formula

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


Re: Excel time formula

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.



LOL!

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

>>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


Re: LOL!

Posted by Aladin Akyurek on March 31, 2001 11:22 PM
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


Re: LOL!

Posted by Dave Hawley on April 01, 2001 12:49 AM
>>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


Re: LOL! london calling

Posted by Barrett (engalnd) on May 14, 2001 11:09 AM
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.





This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.