[Solved] Calculating Overtime from Timetable in Excel

Jululian

Member
Dear All
may i kindly request you to please help me in excel formula as per attached timetable formula
I would be grateful for any assistance you provided
regards
 

Attachments

  • Book2.xlsx
    13.1 KB · Views: 3
Dear All
may i kindly request you to please help me in excel formula as per attached timetable formula
I would be grateful for any assistance you provided
regards
Hello Jululian,

I updated the Morning Overtime, Evening Overtime, and Late Morning formulas.

Morning Overtime: =IFNA(IF(C2<TIME(12,0,0), TIME(12,0,0)-C2, MAX(0, TIME(12,0,0)-C2-TIME(0,5,0))),"")
Evening Overtime: =IF(D2 < C2, D2 + 1 - TIME(22,0,0), IF(D2 > TIME(22,0,0), D2 - TIME(22,0,0), 0))
Late Morning: =IF(C2>TIME(12,0,0), C2-TIME(12,0,0),"00:00")

Overtime calculation.png
To get the desired Time format follow the steps below:

Press, CTRL+1 to get the Format Cells dialog box,
Then, select Custom >> types mm:ss for minute:second format. If you want to show hour:minute:second format use this h:mm:ss

Here is the Excel File:
 

Attachments

  • ExcelDemy-Overtime Calculation.xlsx
    16 KB · Views: 1
Hello Jululian,

I updated the Morning Overtime, Evening Overtime, and Late Morning formulas.

Morning Overtime: =IFNA(IF(C2<TIME(12,0,0), TIME(12,0,0)-C2, MAX(0, TIME(12,0,0)-C2-TIME(0,5,0))),"")
Evening Overtime: =IF(D2 < C2, D2 + 1 - TIME(22,0,0), IF(D2 > TIME(22,0,0), D2 - TIME(22,0,0), 0))
Late Morning: =IF(C2>TIME(12,0,0), C2-TIME(12,0,0),"00:00")

View attachment 1469
To get the desired Time format follow the steps below:

Press, CTRL+1 to get the Format Cells dialog box,
Then, select Custom >> types mm:ss for minute:second format. If you want to show hour:minute:second format use this h:mm:ss

Here is the Excel File:
Dear Mrs. Shamimarita
First of all, I would like to thank you for your time and interest in considering my inquiry. I have some notes marked in yellow if your time allows you to have a look
Regards
 

Attachments

  • Screenshot (1470).png
    Screenshot (1470).png
    44.6 KB · Views: 2
Dear Mrs. Shamimarita
First of all, I would like to thank you for your time and interest in considering my inquiry. I have some notes marked in yellow if your time allows you to have a look
Regards
Hello Jululian,

The formula is working fine. To match your desired output I used mm:ss time format earlier. Here, I used hh:mm time format now.
Please change the time format only.

1. Overtime calculation.png

Here is the updated Excel File:
 

Attachments

  • ExcelDemy-Overtime Calculation.xlsx
    16 KB · Views: 3

Online statistics

Members online
0
Guests online
32
Total visitors
32

Forum statistics

Threads
337
Messages
1,470
Members
624
Latest member
duytoi
Top