• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculate Over time

sg1822

New Member
Dear all,
i want to calculate over time in a month on weekly basis.
criteria is 5 days duty in between every Wednesday to Tuesday ( i.e starting from Wednesday & end with Tuesday , then again continued from next Wednesday )

please see attached file.

WedThuFriSatSunMonTueWedThuFriSatSunMonTueWedThuFriSatSunMonTueWedThuFriSatSunMonTueWedThuFriDNOFFOT
Date12345678910111213141516171819202122232425262728293031
OFFOFFDDNNOFFOFFDDNNOFFOFFDDNNOFFOFFDDNNOFFOFFDDNNOFF1010112
OFFDDNNOFFOFFDDNNOFFOFFDDNNOFFOFFDDNNOFFOFFDDNNOFFOFF1010113
DDNNOFFOFFDDNNOFFOFFDDNNOFFOFFDDNNOFFOFFDDNNOFFOFFD1110104
DNNOFFOFFDDNNOFFOFFDDNNOFFOFFDDNNOFFOFFDDNNOFFOFFDD1110103
NNOFFOFFDDNNOFFOFFDDNNOFFOFFDDNNOFFOFFDDNNOFFOFFDDN1011102
NOFFOFFDDNNOFFOFFDDNNOFFOFFDDNNOFFOFFDDNNOFFOFFDDNN1011102
 

Attachments

  • OT_CALCULATION_WEEKWISE.xlsx
    19.4 KB · Views: 3
Thank very much Vletm, & I appreciates your efforts in this regards.

The Sample file is for the month of DEC-21 , in which month starts from wed. , but what will happen when we used for other months in future.
we have to specify range manually every time, some times week ( duty schedule ) will start in end of the month & complete in the date of next month.
Hence, if we get formula which will work on dynamic range, will be better for us
 
sg1822
You expected formula solution.
Have You thought - how to solve last days of month and beginning of next month?
How about N-shifts? eg if it starts 18:00 and ends 06:00 ... then should that shift split to two days ... to two weeks?
... could there be four + four days case? ( Sat-Tue + Wed-Sat --- without any OFFs --- without any OTs )?
I would do this myself ... different way.
 
Dear vletm,
Yes we expected formula solution,
if i change date then Days should be changed according to calendar.
it doesn't matters N-shift starts todays (28 th NOV) and end tomorrow , same will be known or calculated as 28th Night shift
OT have been calculated granted if less than 3 off in between Wed-Tue only.
 
Back
Top