Share via

Excel-Date & Time Formula to Calculate Night Differential Hours

Anonymous
2022-04-14T12:29:52+00:00

Dear Team,

I have a problem and unable to find a solution to make a formula in Excel:

My Problem:

I want to Calculate Calculate Night Differential Hours ( Time Start from 10PM till 6 am Next morning)

  1. ND hours & Excess of ND hours

We have schedule shift and Actual Punch In & OutTime e.g.

Regular Shift 9 am - 6pm
ND-10pm -6 am Worked-Punch In & Out 9 am - 4 am
OT hrs OT rate
Normal Hours (9-6) - 1 hr break 8 8 100
Excess of OT 10 10 125
Excess ND 8 6 6 12.5

2nd example:

Regular Shift 4 pm - 1 am
ND-10pm -6 am Worked-Punch In & Out 4 pm - 2 am
OT hrs OT rate
Normal Hours (4 am-1 am) - 1 hr break 8 100
Excess of OT 1 125
ND First 8 (with in shift) 3 10
Excess ND 8 (beyond shift) 1 12.5

3rd example:

Regular Shift 4 pm - 1 am
ND-10pm -6 am Worked-Punch In & Out 4 pm - 11:00 PM
OT hrs OT rate
Normal Hours (4 am-1 am) - 1 hr break 6 100
Excess of OT 0 125
ND First 8 (with in shift) 1 10
Excess ND 8 (beyond shift) 0 12.5
Microsoft 365 and Office | Excel | For education | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2022-04-14T16:31:12+00:00

    so, Output is last three columns :

    Start Date End Date Shift In Time(Scheduled) Shift Out Time (Scheduled) Acutal In Time Actual Out Time Total ND Hrs (10 PM to 6 AM) First ND Hrs Excess ND Hrs
    2022-03-02 2022-03-03 17:00 2:00 17:00 5:00 7:00 04:00:00 03:00:00
    2022-03-02 2022-03-03 19:00 4:00 19:00 4:00 6:00 6:00 00:00:00
    2022-03-02 2022-03-02 19:00 4:00 19:00 23:00 1:00 1:00 00:00:00
    2022-03-02 2022-03-02 1:00 10:00 2:00 9:00 4:00 4:00 00:00:00

    2 people found this answer helpful.
    0 comments
  2. Anonymous
    2022-04-14T14:59:53+00:00

    Hi there

    We might need to change the table layout to better suit the calculations.

    2 people found this answer helpful.
    0 comments
  3. Anonymous
    2022-04-14T16:52:10+00:00

    Also there is 1 more condition if employee punch in before schedule time then consider punch in time as scheduled in time..

    0 comments
  4. Anonymous
    2022-04-14T16:02:50+00:00

    Thanks for replying on this actually I need to calculate ND hrs (Night Differential Hrs)

    so, I the below given table :

    My shift Starts at 4 am to 1 am however, I punched in at 4 Am to Punched out at 2 am

    Hence First ND as per Shift should be 3 hours as output as 10 pm to 1 am (shift end) comes to 3 hrs

    Hence Excess ND post 1 am (shift end) till 2 am (punch out) comes to 1 hr

    Note: ND time to be considered from 10 pm till 6 am next morning

    Regular Shift 4 pm - 1 am
    ND 10pm -6 am Worked 4 pm - 2 am
    OT hrs OT rate
    Ordinary/ Basic Pay 8 100
    Excess of OT 1 125
    ND First 8 3 10
    Excess ND 8 1 12.5

    please suggest how to calculate first ND as per shift & Excess ND post shift end (considering ND hours starts 10 pm to 6 am next morning)...

    Many thanks in advance for helping...

    0 comments