Formula for calculating time...with a twist

99jolegg · May 26, 2011

Hi,

I’m looking for some help with a formula. The aim is to convert a time period, past a specified hour into a decimal hour. The purpose of this is to calculate a time period occurring at night, distinguishing it from that time occurring in the day. E.g.

17:53 to 22:38 is 4.8 hours.

 I used the formula: =((HOUR(I14)+MINUTE(I14)/60)+(I14 < G14)-(HOUR(G14)+MINUTE(G14)/60)) for that calculation.

<g14)-(hour(g14)+minute(g14) 60))<="" i=""> <g14)-(hour(g14)+minute(g14) 60))="" for="" that="" calculation.="" <g14)-(hour(g14)+minute(g14)="" calculation.

</g14)-(hour(g14)+minute(g14)></g14)-(hour(g14)+minute(g14)><g14)-(hour(g14)+minute(g14) 60))<="" i=""><g14)-(hour(g14)+minute(g14) 60))="" for="" that="" calculation.="" <g14)-(hour(g14)+minute(g14)="" calculation.
However, if I want to then further distinguish what proportion of the time (in the same format) occurs after 21:30, how would I do that? The answer is 1.1 hours but I don’t know how to get Excel to work that out for me. I’d then like 1.1hrs to go in one cell and the remainder, 3.7hrs, to go in another cell.

Any ideas on how I might do this?

Thanks in advance.</g14)-(hour(g14)+minute(g14)></g14)-(hour(g14)+minute(g14)>

GlennUK · May 26, 2011

Could your formula be replaced with:
=(I14-G14)*24
?

If that's OK, then you should see how to answer your own question.

99jolegg · May 26, 2011

Thanks for your reply.

Yes that formula does work, I'm now using that one instead, thanks.

However, I've come across 2 slight problems. I've used the formula you suggested, linking a cell with the start of night time in to get the night time portion. However, if the end time is before the start of night time...i.e. if it finishes at 19:32 and night time starts at 20:55, then I end up with a negative night time. So, to that end, I'm trying to put a conditional formula in to say that if the end time is < 20:55, to leave it at 0. This is all well and simple, but I've already got a condition in that another cell must read a certain value.

How can I put two conditions in a row that must be met? i.e. =IF(E25="P2"+(I25>B2), (I25-B2)*24, 0) I've tried that formula but it doesn't like the "+" as a way of linking two conditions...

Secondly, the formula you suggested is great, until the time goes past midnight. I.e. starting at 21:49 until 00:37. Then it goes to a negative number. Do you know a way of getting around this?

Thanks for your help, it's much appreciated!

99jolegg · May 26, 2011

Cancel that, I forgot about the =IF(AND( part. It now works with both conditions.

The second part still remains though...any ideas how to get it to play ball past midnight?

GlennUK · May 26, 2011

To calculate for times, including ones that go past midnight:

=((I14+IF(I14 < G14,1,0))-G14)*24

99jolegg · May 27, 2011

Perfect, thank you!

Formula for calculating time...with a twist

99jolegg

New Member

Excel Facts

GlennUK

Well-known Member

99jolegg

New Member

99jolegg

New Member

GlennUK

Well-known Member

99jolegg

New Member

Similar threads

Forum statistics

Share this page

Formula for calculating time...with a twist

99jolegg

New Member

Excel Facts

GlennUK

Well-known Member

99jolegg

New Member

99jolegg

New Member

GlennUK

Well-known Member

99jolegg

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock