Working days, working hours - not for the faint of heart...

[jamesducker]

Evening all

It's just gone midnight and my normally persistent and determined brain is telling me to give this one up and go to bed.

This is one for the seriously determined.

I'm trying to make a calculation field which works out the number of WORKING DAYS AND HOURS (ie including only weekdays 9:00 - 17:30) a server has been down, with the following inputs:

- date and time the server went down

- date and time the server came back up (or, if it's still down, the date and time now).

I'm **nearly** all the way there. The calculation below will work in all situations except when the period of downtime includes more a continuous span of more than one complete weekend day. So:

- it works correctly if downtime starts and finishes on the same day

- it works correctly if downtime starts on a weekday and finishes on a weekday later the same working week

- it works if downtime starts on a Friday and finishes on Saturday

- it works if downtime starts on a Sunday and finishes on a Monday

- it works if downtime starts on a Saturday and finishes on a Sunday

- but it doesn't work if downtime starts on a Friday and finishes on a Sunday

- and it doesn't work if downtime starts on a Saturday and finishes on a Monday

And here is the calculation:

Let(restoredOrCurrentDate = If(Downtime restored 1 date = "" or Downtime restored 2 time = "";Get(CurrentDate);Downtime restored 1 date) ;

Let(restoredOrCurrentTime = If(Downtime restored 1 date = "" or Downtime restored 2 time = "";Get(CurrentTime);Downtime restored 2 time) ; 





Let(workingHoursPre1730onDropDay = Max(Time(17;30;0)-Max(Downtime dropped 2 time;Time(9;0;0));0) ;



Let(workingHoursPost0900onRestoreDay = Max(Min(restoredOrCurrentTime;Time(17;30;0))-Time(9;0;0);0) ;



Let(completeDaysInBetween = Max((restoredOrCurrentDate-Downtime dropped 1 date-1);0) ;



Let(completeWeekendDaysInBetween = 0 ;       // not sussed this out yet







Max(



//working hours before 17:30 on the day the connection dropped, unless it dropped on a weekend day

If( DayOfWeek(Downtime dropped 1 date)=7 or DayOfWeek(Downtime dropped 1 date)=1 ; 0 ; workingHoursPre1730onDropDay )

+

//plus working hours after 09:00 on the day the connection was restored, unless it was restored on a weekend day

If( DayOfWeek(restoredOrCurrentDate)=7 or DayOfWeek(restoredOrCurrentDate)=1 ; 0 ; workingHoursPost0900onRestoreDay)

-

//less 8.5 hours (erroneously applied above) if it was a same day fix

If(Downtime dropped 1 date = restoredOrCurrentDate; Time(8;30;0) ; 0)

+

//plus 8.5 hours for all the complete days in between

completeDaysInBetween * Time(8;30;0)

-

//less 8.5 hours for all the complete WEEKEND days in between

completeWeekendDaysInBetween * Time(8;30;0)



;0)







))))))

All I need to do is finish off the let statement in line 6 with something that calculates how many of the 'completeDaysInBetween' were weekend days. But - how??

All help much appreciated. I've attached a file with the calculation and data fields defined. A left-over easter egg to the first person to solve it without using a script ;-)

TVMIA,

James

Ridiculous_calculation_field_problem.fp7.zip

[Søren Dyhr]

Provided no workdays are longer or shorter than others, would this give the days:

Div ( Calculation so far ; Time ( 8 ; 30 ; 0 ) ) returned as number

...while the remaining time is:

Mod ( Calculation so far ; Time ( 8 ; 30 ; 0 ) ) returned as time

--sd