Jump to content

working out the number of working days between 2 dates, when your weekend is NOT Sat-Sun


This topic is 3399 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Dear All,

I was reading what's on the forums for a calculation that can be used to workout the number of weekdays between 2 dates. My problem is that the only day off i have in the week is friday. Can you advice me how to modify the calculation below to work with 6 day work weeks with fridays off rather than saturday and sunday? It would also be greatly appreciated if someone can desifer this number string for me, 0012345501234544012343340123223401111234010012340, what does it mean? :)

Many thanks for your time and help :)

5 * Int ( ( EndDate - StartDate ) / 7 ) +Middle ( "0012345501234544012343340123223401111234010012340" ; 7 * (DayOfWeek ( StartDate ) - 1 ) + DayOfWeek ( EndDate ) ; 1 )

Link to comment
Share on other sites

5 * Int ( ( EndDate - StartDate ) / 7 ) +Middle ( "0012345501234544012343340123223401111234010012340" ; 7 * (DayOfWeek ( StartDate ) - 1 ) + DayOfWeek ( EndDate ) ; 1 )

The first part the calculation [5 * Int ( ( EndDate - StartDate ) / 7 )] is the number of working days for full weeks, since you work 6 days the 5 needs to be changed to 6.The second part [Middle ( "0012345501234544012343340123223401111234010012340" ; 7 * (DayOfWeek ( StartDate ) - 1 ) + DayOfWeek ( EndDate ) ; 1 )] is the number of working days in the partial week remaining.

The string [0012345501234544012343340123223401111234010012340] has a number for each of the 49 combination of start & end days. This might be easier to see if the string is broken up by weeks. The columns are the day of the end date and the rows are the day of the start date.

End day

SMTWTFS

0012345 S

5012345 M

4401234 T

3340123 W Start Day

2234011 T

1123401 F

0012340 S

You will need to create a new string based on 6 days & Friday off. The only in common will be the diagonal where start day and end day are the same will be zeros. The first line (Starting on Sunday) would look like this: 0123455

The [7 * (DayOfWeek ( StartDate ) - 1 ) + DayOfWeek ( EndDate )] calculates the position in the string.

  • Like 1
Link to comment
Share on other sites

Thank you so much for taking the time to help me! Really appreciated :)

I will try and workout the right string for my case and post it back here incase someone else needs it oneday and also for you to point out any mistakes I might make :)

Many thanks again :)

Link to comment
Share on other sites

The string [0012345501234544012343340123223401111234010012340] has a number for each of the 49 combination of start & end days. This might be easier to see if the string is broken up by weeks. The columns are the day of the end date and the rows are the day of the start date.

End day

SMTWTFS

0012345 S

5012345 M

4401234 T

3340123 W Start Day

2234011 T

1123401 F

0012340 S

You will need to create a new string based on 6 days & Friday off. The only in common will be the diagonal where start day and end day are the same will be zeros. The first line (Starting on Sunday) would look like this: 0123455

I am afraid, I still do not quite understand that string. You explained that it is the 49 combinations of start and end dates. Did you mean the combinations of start and end dates of any week in a calendar year? If so, how would the day off be represented differently to the work days in this matrix? How I understand it at the moment is that the numbers, represent the day of the week, sunday being 0, Mon=1, Tue=2...upto Sat=6. And if that was correct, then the first combination in the original string (0012345) doesn't make sense to me because sunday and monday are represented by a 0.

I also tried to work backwards using the 1st line you provided and relating 0123455 of a 6 day week with fridays off to 0012345 for sat-sun off, to understand how to build the rest of the string but I could not. I'm still lost, please help :)

Many Thanks.

Link to comment
Share on other sites

The numbers are the number of days not covered in first part of the calculation for a set of of days, they do not represent days of the week. Lets review the first line I gave you, 0123455. This line is for the start date being a Sunday. The first number is for the end date also being a Sunday. Result is 0.

The second number is for end date being a Monday. Result is 1.

The third number is for end date being a Tuesday. Result is 2.

The fourth number is for end date being a Wednesday. Result is 3.

The fifth number is for end date being a Thursday. Result is 4.

The sixth number is for end date being a Friday. Result is 5, even though you don't work on Friday the number of working days increased for work done on Thursday.

The seventh number is for end date being a Saturday. Result is 5 because you didn't work on Friday.

The next row is for start dates that fall on Mondays.

Lets start with the second number in this row.

The second number is for end date being a Monday. Result is 0.

The third number is for end date being a Tuesday. Result is 1.

The fourth number is for end date being a Wednesday. Result is 2.

The fifth number is for end date being a Thursday. Result is 3.

The sixth number is for end date being a Friday. Result is 4, even though you don't work on Friday the number of working days increased for work done on Thursday.

The seventh number is for end date being a Saturday. Result is 4 because you didn't work on Friday.

The first number is for the end date also being a Sunday. Result is 5.

Continue with this pattern until you complete all 7 rows.

  • Like 1
Link to comment
Share on other sites

Note also that this is not really a calculation as such; it's just a lookup of a pre-calculated result from a 7x7 matrix.

In order to actually calculate the result, try =

Let ( [

n = EndDate - StartDate + 1 ;

w = Div ( n ; 7 ) ;

r = Mod ( n ; 7 ) ;

d = Mod ( StartDate + 1 ; 7 )

] ;

6 * w + r - ( d + r > 6 )

)

  • Like 3
Link to comment
Share on other sites

I have a similar question. We are running a B&B and are working with different rates on specific days. In this case I guess I need two calculations:

number of days stayed on: sun, monday, tuesday, wednesday, thursday

number of days stayed on: friday, saturday

It took me hours to look and search. Above solution puts me to a direction, but I am not still getting it. Can someone help in how to do calculations for the two options?

Thanks from a desperate B&B owner :)

Link to comment
Share on other sites

You ned a table of rates where each record contains:

start date

end date

room type

amount

<could be more amounts>

Then use a relationship based on the room type and dates to look up the rate.

Link to comment
Share on other sites

I need two calculations:

number of days stayed on: sun, monday, tuesday, wednesday, thursday

number of days stayed on: friday, saturday

Try:

RegularDays =

Let ( [

n = EndDate - StartDate + 1 ;

w = Div ( n ; 7 ) ;

r = Mod ( n ; 7 ) ;

d = DayOfWeek ( StartDate ) ;

o = Case ( r ; ( d + r > 6 ) + ( d + r > 7 ) )

] ;

5 * w + r - o

)




WeekendDays =


Let ( [

n = EndDate - StartDate + 1 ;

w = Div ( n ; 7 ) ;

r = Mod ( n ; 7 ) ;

d = DayOfWeek ( StartDate ) ;

o = Case ( r ; ( d + r > 6 ) + ( d + r > 7 ) )

] ;

2 * w + o

)

  • Like 3
Link to comment
Share on other sites

  • 1 month later...
  • Newbies

The first part the calculation [5 * Int ( ( EndDate - StartDate ) / 7 )] is the number of working days for full weeks, since you work 6 days the 5 needs to be changed to 6.The second part [Middle ( "0012345501234544012343340123223401111234010012340" ; 7 * (DayOfWeek ( StartDate ) - 1 ) + DayOfWeek ( EndDate ) ; 1 )] is the number of working days in the partial week remaining.

The string [0012345501234544012343340123223401111234010012340] has a number for each of the 49 combination of start &amp; end days. This might be easier to see if the string is broken up by weeks. The columns are the day of the end date and the rows are the day of the start date.

End day

SMTWTFS

0012345 S

5012345 M

4401234 T

3340123 W Start Day

2234011 T

1123401 F

0012340 S

You will need to create a new string based on 6 days &amp; Friday off. The only in common will be the diagonal where start day and end day are the same will be zeros. The first line (Starting on Sunday) would look like this: 0123455

The [7 * (DayOfWeek ( StartDate ) - 1 ) + DayOfWeek ( EndDate )] calculates the position in the string.

Hello, I

Link to comment
Share on other sites

  • Newbies

I´ve made the four routes,

Could you please check if I made something wrong???

Screen%2520shot%25202012-06-29%2520at%25

The calculations:

Route A:

6 * Int ( ( EndDate - StartDate ) / 7 ) +Middle ( "0012345601234555012344450123334501222345011123450" ; 7 * (DayOfWeek ( StartDate ) - 1 ) + DayOfWeek ( EndDate ) ; 1 )

Route B:

3 * Int ( ( EndDate - StartDate ) / 7 ) +Middle ( "0011223301122322001122230112112200111223010011220" ; 7 * (DayOfWeek ( StartDate ) - 1 ) + DayOfWeek ( EndDate ) ; 1 )

Route C:

5 * Int ( ( EndDate - StartDate ) / 7 ) +Middle ( "0011234501123444001234450123334401222334011122340" ; 7 * (DayOfWeek ( StartDate ) - 1 ) + DayOfWeek ( EndDate ) ; 1 )

Route D:

3 * Int ( ( EndDate - StartDate ) / 7 ) +Middle ( "0001122300112233011222220011222301111122001112230" ; 7 * (DayOfWeek ( StartDate ) - 1 ) + DayOfWeek ( EndDate ) ; 1 )

Route E:

5 * Int ( ( EndDate - StartDate ) / 7 ) +Middle ( "0012345501234544012343340123223401211234010012340" ; 7 * (DayOfWeek ( StartDate ) - 1 ) + DayOfWeek ( EndDate ) ; 1 )

Thank you very much for your help

Link to comment
Share on other sites

  • 2 years later...

Try:

RegularDays =

Let ( [
n = EndDate - StartDate + 1 ;
w = Div ( n ; 7 ) ;
r = Mod ( n ; 7 ) ;
d = DayOfWeek ( StartDate ) ;
o = Case ( r ; ( d + r > 6 ) + ( d + r > 7 ) )
] ;
5 * w + r - o
)
WeekendDays =

Let ( [
n = EndDate - StartDate + 1 ;
w = Div ( n ; 7 ) ;
r = Mod ( n ; 7 ) ;
d = DayOfWeek ( StartDate ) ;
o = Case ( r ; ( d + r > 6 ) + ( d + r > 7 ) )
] ;
2 * w + o
)

Can you please explain what is "O"? I don't understand that line "o = Case ( r ; ( d + r > 6 ) + ( d + r > 7 ) )".

 

I am trying to work with this calculation to calculate different work rota patterns for a 4, 8hr shifts, (24/7) operation. I'm going for a calculation that determines if a person who is currently on crew A for example is on a working day or a weekend, while considering that these weekends change every work week depending on Crews B,C and D. Your help will be very much appreciated.

Link to comment
Share on other sites

o, as far as I can remember(!), calculates the number of Fridays and Saturdays included in the remainder of days r. It can be either 0 or 1 or 2.

 

Great, thank you for your prompt response.

 

I've been testing these 2 calculations and i found that there is a problem if the start date is 1/11/2014 and end date is 30/11/2014. November 2014 is 30 days with 9 Fridays and Saturdays which means that regular days should be 21 and weekend days should be 9. With the above calculations I get 20 and 10 respectively.

 

I also tried having the start and end dates as Saturday the 6th of December 2014 to see what the outcome would be. I expected to get regular days 0 and weekend days 1. However I got -1 regular days and 2 for weekends.

 

Am I missing something or doing something wrong?

Link to comment
Share on other sites

This topic is 3399 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.