Night Differential And Night Differential OT Formula

[AliGW]

Explain your outcome of 5 for row 14 - what are your calculation steps? Logic and maths, please.

 

[vletm]

jameswan​

I would use only times with calculating time.
I would use different layout with this.
If You're interesting about different layout then let me know.

 

[jameswan]

Explain your outcome of 5 for row 14 - what are your calculation steps? Logic and maths, please.

Hi! Thank you for asking for clarification. Here's how I calculated the Approved NDOT for Row 14:

1. Approved Time Out:
   Starting from the Scheduled Time Out at 21:00, the Approved OT is 6 hours, which sets the Approved Time Out to 3:00.
2. ND (Night Differential) Hours:
   Night Differential is applicable from 22:00 to 6:00.
3. Approved NDOT Hours:
   • From 21:00 to 22:00, no hours fall under ND.
   • From 22:00 to 3:00, 5 hours overlap with the ND period.

So the calculation is straightforward:
The ND hours (22:00–6:00) overlap with the Approved OT hours (22:00–3:00), resulting in 5 hours of Approved NDOT.

Let me know if this aligns with your understanding or if there’s something I’ve overlooked.

 

[jameswan]

jameswan​

I would use only times with calculating time.
I would use different layout with this.
If You're interesting about different layout then let me know.

Yes, please. I would appreciate that. I'm open to suggestions and ideas.

 

[AliGW]

Do you want to go with @vletm and completely redesign this? If so, I won't waste my time creating anoher complex formula that has to overcome your layout and data defects.

 

[jameswan]

Do you want to go with @vletm and completely redesign this? If so, I won't waste my time creating anoher complex formula that has to overcome your layout and data defects.

No, please. I believe your ideas could be the key to solving this issue within the current structure. I know the layout might not be ideal, but it would really help if you could continue assisting me in working with what I already have. Thank yooouu

 

[AliGW]

This seems to work in the latest sample workbook (and is my last contribution):

=IF(N4-INT(N4)=(L4/24),0,IF(AND(N4-INT(N4)>G4-INT(G4),G4-INT(G4)>=6/24),0,IF(G4>N4-INT(N4),MAX(N4,22/24)-MAX(G4,22/24),0)))

It's up to you to tweak it further if you find circumstances that fail - this is for your own benefit, as you need to (a) understand it and (b) be able to manipulate it going forward. You can't keep coming back here for minor tweaks - this would be of no use to you professionally. Hope this helps.

 

[AliGW]

You have seen what I posted earlier this morning. Please confirm that this did what you want (don't leave it hanging without any acknowledgement, please).

 

[jameswan]

You have seen what I posted earlier this morning. Please confirm that this did what you want (don't leave it hanging without any acknowledgement, please).

Hi, apologies. It didn't work but I am figuring out how I am going to modify itttt

 

[AliGW]

Well, it DOES work in the sample file!!! I can't provide and test solutions for what I can't see.

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	T​	U​
4​	2​	2:00​
5​	0​	0:00​
6​	0​	0:00​
7​	0​	0:00​
8​	0​	0:00​
9​	0​	0:00​
10​	​	0:00​
11​	​	0:00​
12​	​	0:00​
13​	​	0:00​
14​	5​	5:00​
15​	8​	8:00​
16​	4​	4:00​
17​	2​	2:00​
18​	0​	0:00​

Sheet: Schedule (2)