Looking for a formula with Times

[Arkine]

Hello everyone,

I would like to find a formula to create "night work", which means any period for at least six (6) consecutive hours between 6 P.M and 6 A.M. the following day.
So only the time starting from 18:00 will start to count, the time has to take into consideration the break time. If he begins to work at 16:00, starting from 18h00 we count his hours, then if he works more than 6 hours (deducing his break time) it is then considerated as night work.

Here are 3 examples, anyone has any idea how to create the night work formula? ( the work time is not necessary, i've just put it to understand the problem better)

Thank you very much for your help!
Best,
Arkine

 

[Clive Muchemwa]

Hi Arkine,

I am working on a detailed explanation on how I came up with the formula but find the formula below. The formula assumes you have your data is input as follows;

Time In - Cell "A2"
Time Out - Cell "B2"
Break Time - Cell "C2"
Work Time - Cell "D2"

Formula:
=IF((IF(A2>=0.75,(IF(B2>A2,((HOUR(B2)*60)+(MINUTE(B2)))/1440-((HOUR(A2)*60)+(MINUTE(A2)))/1440-((HOUR(C2)*60)+(MINUTE(C2)))/1440,(((HOUR(B2)*60)+(MINUTE(B2)))/1440)+1-((HOUR(A2)*60)+(MINUTE(A2)))/1440-((HOUR(C2)*60)+(MINUTE(C2)))/1440)),(IF(B2>A2,((HOUR(B2)*60)+(MINUTE(B2)))/1440-0.75-((HOUR(C2)*60)+(MINUTE(C2)))/1440,(((HOUR(B2)*60)+(MINUTE(B2)))/1440)+1-0.75-((HOUR(C2)*60)+(MINUTE(C2)))/1440))))<0.25,0,IF(A2>=0.75,(IF(B2>A2,((HOUR(B2)*60)+(MINUTE(B2)))/1440-((HOUR(A2)*60)+(MINUTE(A2)))/1440-((HOUR(C2)*60)+(MINUTE(C2)))/1440,(((HOUR(B2)*60)+(MINUTE(B2)))/1440)+1-((HOUR(A2)*60)+(MINUTE(A2)))/1440-((HOUR(C2)*60)+(MINUTE(C2)))/1440)),(IF(B2>A2,((HOUR(B2)*60)+(MINUTE(B2)))/1440-0.75-((HOUR(C2)*60)+(MINUTE(C2)))/1440,(((HOUR(B2)*60)+(MINUTE(B2)))/1440)+1-0.75-((HOUR(C2)*60)+(MINUTE(C2)))/1440))))

Hope this helps in the meantime. Hoping to finish that detailed explanation within the next 24 hours.

Kind regards,
Clive Muchemwa

 

[Arkine]

It looks complex but thank you so much! I'll try

I guess in your formula the 6pm and 6am cannot be variables.

 

[Clive Muchemwa]

Hi Arkine,

I have had some level of difficulty doing calculations on dates and time. That’s because the value of time used when making calculations is not the hours and minutes at face value (this will make sense a little later). So, the first thing is to understand the value of time in Excel.

Simply put 24 hours = 1440 minutes = 1

That means 00:00 (12:00:00 AM) = 0.0000 and 23:59 (11:59:00 PM) = 0.9993

To simplify time calculation we need to convert time to a numerical value. We can do this by either converting the time to hours or minutes and express it as a fraction of a complete day. In the formula, I converted to minutes. Here is an example;

Converting 23:59 to a numerical value
23:59 is simply 23 hours and 59 minutes. To convert to minutes, we just convert the hours to minutes i.e. 23*60 which is equals to 1380. So, 23:59 in hours is 1439 minutes. To convert that to a numerical value we just divide by 1440 minutes (total minutes in a day) to get 0.9993.

Now let us assume 23:59 is in cell “B2”

Using Excel formulas, to get the hour we use =HOUR(B2) and to get the minute we use =MINUTE(B2). So =HOUR(B2) will return the value 23 whilst =MINUTE(B2) will return the value 59. To get the total minutes we use the formula =HOUR(B2)*60+MINUTE(B2).

To get the numerical value we then use the formula =(HOUR(B2)*60+MINUTES(B2))/1440

Calculating the time difference
Now assuming your Time In is in cell “A2”, Time Out in cell “B2” and Break Time in cell “C2”, your total time worked would simply be =C2-A2-B2. However, you are going to use the calculated numerical value for each cell;

A2 --- =(HOUR(A2)*60+MINUTES(A2))/1440
B2 --- =(HOUR(B2)*60+MINUTES(B2))/1440
C2 --- =(HOUR(C2)*60+MINUTES(C2))/1440



So your formula for the time difference would look something like this;
=(HOUR(C2)*60+MINUTES(C2))/1440-(HOUR(A2)*60+MINUTES(A2))/1440-(HOUR(B2)*60+MINUTES(B2))/1440

But, if some work until midnight, the formula above will return a negative numerical value which will not be recognised by the time format. So we need to a 24 hours such that 01:00 would be recognised as 25:00 (I hope this makes sense). So if one works from 11pm to 1am the calculation is 25:00-23:00.

So we introduce an IF function. So we know if the numerical value of time out is less numerical value of time in, we get a negative value on our difference and returns an error or ####. So the logical test on our IF function would B2>A2. If the logical test is true i.e. value of time out is greater than value of time in, then we do not need to add the 24 hours. However, if its false we need to. So the formula with the IF function will look something like this;

=IF(B2>A2,(HOUR(C2)*60+MINUTES(C2))/1440-(HOUR(A2)*60+MINUTES(A2))/1440-(HOUR(B2)*60+MINUTES(B2))/1440,(HOUR(C2)*60+MINUTES(C2))/1440-(HOUR(A2)*60+MINUTES(A2))/1440-(HOUR(B2)*60+MINUTES(B2))/1440+1)

Now our formula is able to calculate time worked. However, we want to calculate night work which begins at 18:00. So we need to introduce another IF function where our logical test is whether time in is at or after 18:00. If so, then we apply the formula above, if not we need to replace time in with the numerical value of time in to the value of 18:00 which is 0.75

So the formula would be broken down as follows;

=IF(logical test,[value if true],[value if false])

Logical test:
A2>=0.75

[value if true]:
IF(B2>A2,(HOUR(C2)*60+MINUTES(C2))/1440-(HOUR(A2)*60+MINUTES(A2))/1440-(HOUR(B2)*60+MINUTES(B2))/1440,(HOUR(C2)*60+MINUTES(C2))/1440-(HOUR(A2)*60+MINUTES(A2))/1440-(HOUR(B2)*60+MINUTES(B2))/1440+1)

[value if false]:
IF(B2>A2,(HOUR(C2)*60+MINUTES(C2))/1440-(HOUR(A2)*60+MINUTES(A2))/1440-0.75,(HOUR(C2)*60+MINUTES(C2))/1440-(HOUR(A2)*60+MINUTES(A2))/1440-0.75+1)

Note: we have replaced (HOUR(A2)*60+MINUTES(A2))/1440 with 0.75 when the logical test is false so that the formula only calculates the difference from 18:00.

So our formula now looks like this;

=(IF(A2>=0.75, IF(B2>A2,(HOUR(C2)*60+MINUTES(C2))/1440-(HOUR(A2)*60+MINUTES(A2))/1440-(HOUR(B2)*60+MINUTES(B2))/1440,(HOUR(C2)*60+MINUTES(C2))/1440-(HOUR(A2)*60+MINUTES(A2))/1440-(HOUR(B2)*60+MINUTES(B2))/1440+1), IF(B2>A2,(HOUR(C2)*60+MINUTES(C2))/1440-(HOUR(A2)*60+MINUTES(A2))/1440-0.75,(HOUR(C2)*60+MINUTES(C2))/1440-(HOUR(A2)*60+MINUTES(A2))/1440-0.75+1))

Lastly, night work needs to be at least 6 hours. So we need to introduce the last IF function. If the value returned by the formula above is less than 6 hours then return the value 0 otherwise return the value calculated. However, we need to use the numerical value for 06:00 which is 0.25.

So the formula would be broken down as follows;

=IF(logical test,[value if true],[value if false])

Logical test:
IF((IF(A2>=0.75, IF(B2>A2,(HOUR(C2)*60+MINUTES(C2))/1440-(HOUR(A2)*60+MINUTES(A2))/1440-(HOUR(B2)*60+MINUTES(B2))/1440,(HOUR(C2)*60+MINUTES(C2))/1440-(HOUR(A2)*60+MINUTES(A2))/1440-(HOUR(B2)*60+MINUTES(B2))/1440+1), IF(B2>A2,(HOUR(C2)*60+MINUTES(C2))/1440-(HOUR(A2)*60+MINUTES(A2))/1440-0.75,(HOUR(C2)*60+MINUTES(C2))/1440-(HOUR(A2)*60+MINUTES(A2))/1440-0.75+1)))<0.25

[value if true]:
0

[value if false]:

(IF(A2>=0.75, IF(B2>A2,(HOUR(C2)*60+MINUTES(C2))/1440-(HOUR(A2)*60+MINUTES(A2))/1440-(HOUR(B2)*60+MINUTES(B2))/1440,(HOUR(C2)*60+MINUTES(C2))/1440-(HOUR(A2)*60+MINUTES(A2))/1440-(HOUR(B2)*60+MINUTES(B2))/1440+1), IF(B2>A2,(HOUR(C2)*60+MINUTES(C2))/1440-(HOUR(A2)*60+MINUTES(A2))/1440-0.75,(HOUR(C2)*60+MINUTES(C2))/1440-(HOUR(A2)*60+MINUTES(A2))/1440-0.75+1))

Combine these into one function and we have our formula.

Hope this helps.

Kind regards,
Clive Muchemwa

 

[Clive Muchemwa]

It looks complex but thank you so much! I'll try

I guess in your formula the 6pm and 6am cannot be variables.

They can be, you can try it out and tell me what you get.

 

[Arkine]

Took me a while to understand all of it, Thank you for your detailed explaination!
Just a few errors:
- when i tried it out, the basic value it gives out is 6:00:00 of night work if there's no input, I've just put an 'IF" ontop of the formula to say if values are blank, return blank
- If the time goes past to 6AM next day it still considers it as night work (ex of what should happen: he comes at 23:30PM and leaves at 10AM30 next day, the night work cell shows 11 hours worked, while it should show only the time worked from 18:00 to 6am next day so it should be 6:30 night work.)

Time in	Time out	Worked total	Night work
23:30	10:30	11:00	6:30

Thanks again!