How to calculate night time over midnight

**defiesta** · 01-04-2013, 04:01 AM

Hi all,

How to calculate night time over midnight. Night time means 22:00-06:00.
Please see attached excel file.

Formula at D18 is ok when stop time is between 24:00. But stop time above 24:00 result is 0:00.
This is time only situation.

Please help me for solve this problem.

salary_production12_12test.xlsm

**dogberry** · 01-04-2013, 04:06 AM

Hi

Your file is password protected try formatting your cells in [h]:mm format.

Chris

**RHCPgergo** · 01-04-2013, 04:06 AM

Hi,

your attached worksheet is password protected, please remove it.

edit: the pw protection I mean.

**Kevin UK** · 01-04-2013, 04:09 AM

Hi defiesta

Why upload a password protected sheet!

How about: =MOD("06:00"-"22:00",1) and format as [h]:mm

**defiesta** · 01-04-2013, 04:33 AM

My bad, password is removed.

**dogberry** · 01-04-2013, 04:41 AM

Hi

You have some cells formatted at hh:mm change them all to [h]:mm it should then work for you.

Chris

**defiesta** · 01-04-2013, 04:45 AM

Originally Posted by dogberry

Hi

You have some cells formatted at hh:mm change them all to [h]:mm it should then work for you.

Chris

Not works!

**olasa** · 01-04-2013, 09:45 PM

Well, it calculates the night time between 22:00-06:00, in most cases.
But not in all cases. And, the formula is a bit messy.
//Ola

="08:00"-(IF(("08:00"-MOD(D9+"02:00";1))>0;MOD(D9+"02:00";1);0)+IF(("08:00"-MOD(D10+"02:00";1))>0;"08:00"-MOD(D10+"02:00";1);0))
D9: Start time
D10: End time

Example:
This works: 22:00-06:00 --> 8hrs, 00:00-06:00 --> 6hrs, 22:00-24:00 or 22:00-00:00 --> 2hrs
But not: 05:00-23:00 --> Error

**defiesta** · 01-06-2013, 11:05 PM

Originally Posted by olasa

Well, it calculates the night time between 22:00-06:00, in most cases.
But not in all cases. And, the formula is a bit messy.
//Ola

="08:00"-(IF(("08:00"-MOD(D9+"02:00";1))>0;MOD(D9+"02:00";1);0)+IF(("08:00"-MOD(D10+"02:00";1))>0;"08:00"-MOD(D10+"02:00";1);0))
D9: Start time
D10: End time

Example:
This works: 22:00-06:00 --> 8hrs, 00:00-06:00 --> 6hrs, 22:00-24:00 or 22:00-00:00 --> 2hrs
But not: 05:00-23:00 --> Error

Excel: formula contains error!