Workday Function - How to change to a 6 day workweek

[dgbear]

Excel 2002 seems to default to a 5 day workweek (Monday to Friday).

How do you change excel to base workday calculations on a 6 day work week (Monday to Saturday) ?

Thanks in advance,

DG

 

[barry houdini]

What are you trying to do, are you trying to count the number of workdays from a specific date but also include Saturdays?

 

[fairwinds]

Welcome to the board!

With start date in A1 and end date in B1, try:

=B1-A1+1-(INT((B1-1)/7)-INT((A1-1)/7)+INT(WEEKDAY(A1-1)/7))

 

[barry houdini]

...or...

=B1-A1-INT((B1-A1+WEEKDAY(A1-1))/7)+1

 

[fairwinds]

Yeah, forgot that shorter version. Thanks Barry.

Now reading this again I think maybe it is the WORKDAY function we should replicate, i.e.

A1 = start date
B1 = number of days

but only count Sunday as weekend.

The best I could think of is:

=SMALL(IF(WEEKDAY(ROW(INDIRECT(A1&":"&WORKDAY(A1,B1))))<>1,ROW(INDIRECT(A1&":"&WORKDAY(A1,B1)))),B1+1)

Confrmed with Ctrl + shift + enter.

But does anyone know of a simpler solution ( on the "/7" theme) ?

I have a vague idea I've seen that a while back, but I cannot think of where.

 

[barry houdini]

Fairwinds,

I get some errors using that formula, or inaccurate results when A1 is a Sunday

This should work

=INT((B1+WEEKDAY(A1,3))*7/6)+A1-WEEKDAY(A1,3)-(WEEKDAY(A1)=1)

although I expect there's something a little easier

 

[fairwinds]

Yes, My formula needs to be:

=SMALL(IF(WEEKDAY(ROW(INDIRECT(A1&":"&WORKDAY(A1,B1)+1)))<>1,ROW(INDIRECT(A1&":"&WORKDAY(A1,B1)+1))),B1+1-(WEEKDAY(A1)=1))


I'm not entirely happy with your formula

E.g. Sunday 10/10-2004 + 6 days gives Sunday 17/10-2004

I would expect Saturday 16/10 as my formula gives.

Hopefully it could be adjusted as your suggestion is much more efficient.

 

[Kundepuu]

Here is UDF
use like C1=WorkDays(A1,B1) where A1=start date and B1=end date

Function WorkDays(StartDate As Long, EndDate As Long) As Long
Dim days As Long
Dim Counter As Long
    For days = StartDate To EndDate
        If Weekday(days, vbMonday) < 7 Then
            Counter = Counter + 1
        End If
    Next days
    WorkDays = Counter
End Function

 

[barry houdini]

I'm not entirely happy with your formula

E.g. Sunday 10/10-2004 + 6 days gives Sunday 17/10-2004

I would expect Saturday 16/10 as my formula gives.

Hopefully it could be adjusted as your suggestion is much more efficient.

Mmm....I thought I'd checked all the possibilities but obviously not - I had a feeling it wasn't quite the best way anyway - back to the drawing board :x

Thanks for checking it

edit: this is the best I could come up with - B1 should be bigger than 0

=INT(B1/6)*7+A1+MOD(B1,6)+INT((WEEKDAY(INT(B1/6)*7+A1)-2+MOD(B1,6))/6)

 

[fairwinds]

That seems to work just fine.

Well done!