4 Day Workweek

[Guest]

I've got a project, that is scheduled out as Project Day 1, 2, 3, 4, 5, 6
etc. I want to write a formula that will add a column showing the calender
date that corresponds to each of these, once I key in a start date. They'll
all reference that start date. That, I can do. What is going to give me
fits, is that we are on a 4 day work week. SO, Project Day 5, is calendar
day 8, 6=9, 7=10, 8=11. Then, project Day 9=15, 10=16 and so forth.

Now, I used to use a formula calculated a time difference, based on an 8
hour day and work week. I could calculate the business hours from 3:00 on
a Friday to noon on a Monday. I'm thinking I'll need the same type of
formula here, somehow working in a 4 day workweek.

Does this make any sense? Do you have any idea how I can do this?

I only have 500 lines to apply this to, so I could go calculate it all and
manually re-type them, but I expect the Project Days to change over time, and
I want the calendar dates to change with them.

Thanks.

 

[Gary Brown]

From your example below, it looks like you work Friday to Monday (4 days) so
that's how I'll taylor this.
Use the Weekday function. If it isn't working, you'll have to activate it
by going to TOOLS / ADDINS and clicking on 'Analysis Toolpak'.
The Weekday function gives values to each day of the week. Saturday is 0,
Sunday is 1, Monday is 2, etc.

In A1 thru A5 put 1,2,3,4 and 5 <= Project Days
In B1 put 01/07/2005 <=First day of project
In B2 put the following formula and then copy it down to B3 thru B5...
=IF(WEEKDAY(B1)=2,B1+4,B1+1)

What you will see is...
1 01/07/2005 <=== Friday (weekday value of 7)
2 01/08/2005 <=== Saturday (weekday value of 0)
3 01/09/2005 <=== Sunday (weekday value of 1)
4 01/10/2005 <=== Monday (weekday value of 2)
5 01/14/2005 <=== Friday (weekday value of 7)

To have a Monday thru Thursday workweek, change the '2' in the above formula
to 5.
To have a Tuesday thru Friday workweek, change the '2' in the above formula
to 6.
As Yule Brenner would say...etc, etc, etc.

HTH,
Gary Brown