# 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

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

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

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

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

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

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

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