Calculating Service Time with various requirements

[Balaraman.Chennai]

Hi,

Greeting for the day!!
Here I post this Scenario to get help or Idea from you guys..

I Need to calculate the Exact Service Time from 'Start Date & Time' to 'End Date & Time' with following requirements...
1> Normal Business Hours is 0800 to 2230
2> Exclude the Weekends and Holidays
3> Day Light Saving Business Hours is 0800 to 2130
4> When Offshore Off, the Business Hours is 0800 to 1730 [IF Day Light 0800 to 1700]
5> When Onshore Off, the Business Hours is 1330 to 2230 [IF Day Light 1230 to 2130]

Action Taken: I have succeed in first two requirements by following excel formula

=(NETWORKDAYS(A2,B2,Holidays)-1)*("22:30"-"8:00")+IF(NETWORKDAYS(B2,B2,Holidays),MEDIAN(MOD(B2,1),"22:30","8:00"),"22:30")-MEDIAN(NETWORKDAYS(A2,A2,Holidays)*MOD(A2,1),"22:30","8:00")
>Assigned separate column to store the holiday dates and declares as "Holidays"

Need Advice/Help: How to Daylight Saving/Off days to calculate the Service Time.
>We can assign separate columns to declare Day Light Saving Dates/Offshore Off Dates/Onshore Off Dates..
Note: If you are suggest VBA then, please clarify me to correct myself in future

 

[Luke M]

Here's one way to do it. I setup a couple of Named Formulas to claculate StartTime and EndTime for each row.

 

[Balaraman.Chennai]

Hi Luke

Thank you for your response

But the Off date will not be for entire from start date to end date

 

[Balaraman.Chennai]

Hi,

Please find the attachment, lets my requirements can be explained through the Date Assignments

Refer "Exception" sheet and I have declare the requirements as below:

A:A ~ Holidays
C:C ~ OnsiteWeeklyOff
E:E ~ OffshoreWeeklyOff
G:G ~ DayLightSaving

Above required date has been filled by User and they need end results as Service Time with below requirement with start date and end date

1> Normal Business Hours is 0800 to 2230
2> Exclude the Weekends and Holidays
3> Day Light Saving Business Hours is 0800 to 2130
4> When Offshore Off, the Business Hours is 0800 to 1730 [IF Day Light 0800 to 1700]
5> When Onshore Off, the Business Hours is 1330 to 2230 [IF Day Light 1230 to 2130]


Thanks