How to calculate sunday hours from date range?

[JariV]

Hi,

I have another newbie question, it's probably answered already, just couldn't find it...

I have a file containing workshift data, defining each workshift by following fields: StartDate, StartTime, EndDate and EndTime.

Duration of a single workshift can be anything between 2hrs and 20 days.

I'm trying to create a calculation field, which would show the hours that elapse during Sundays (00:00-24:00) within each workshift.

I'd be extremely greatful, if someone could point me into right direction as how to approach this.

Thanks in advance,

Jari V

[Osman]

Hi Jari,

May be fallowing calculation helps you.

Let me explain the algorithm. We have 4 cases first one is start and end time is sunday so get the time passes from start and end times then add other sundays if it exist between date range.

The second is the case that start date is sunday and end is not. In this case we should add sunday time for first date and add other sundays if it exist between date range.

Third case is similar with second.

Last case is calculates only sundays between two dates.

(note : i use 60*60*24 for determine 24 hours (for day) in seconds and calculation result should be time)

Case(

//case 1: start end end time on sundays and extra sundays between two days

DayOfWeek ( startDate ) = 1 and DayOfWeek ( endDate ) = 1;

Time ( 24; 0; 0)-startTime + (endTime) + Int ( (endDate-startDate-2)/7 ) * 60*60*24;

//case 2: starting say sunday and end is not

DayOfWeek ( startDate ) = 1;

Time ( 24; 0; 0)-startTime + Int ( (endDate-startDate-1)/7 ) * 60*60*24;

//case 3: ends on sunday

DayOfWeek ( endDate ) = 1;

(endTime) + Int ( (endDate-startDate-1)/7 ) * 60*60*24;

//case 4: no sunday on start and end date

Int ( (endDate-startDate + DayOfWeek ( startDate )-2)/7 ) * 60*60*24)

Adam Djuby

[Osman]

there must be a small change in calculation

i forget start and end dates are same and sunday!

case 0 will solve it:

//case 0:

DayOfWeek ( startDate ) = 1 and DayOfWeek ( endDate ) = 1 and startDate=endDate;

endTime-startTime;

... (other cases)

Adam Djuby

[JariV]

Hi Adam,

Thanks a lot for your help - looks exactly like what I needed.

Jari V