Back to Dates in Excel archive index

Back to archive home

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

i.e.

start 7:00AM

end 5:15PM

Total 10:15 I need to convert that to 10.25

Appreciate in advance any help.

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

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

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

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

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

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

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

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

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.

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.