Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with working days
How can I ensure that if I add a number of days to a date (which is a working
date) the result will also be a work date -- Thanks, Jill |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with working days
something like this should do the trick
Dim mydate As String mydate = Format(Date + 2, "dddd") If mydate = "Saturday" Then mydate = Format(Date + 4, "dddd") If mydate = "Sunday" Then mydate = Format(Date + 4, "dddd") "JillC" wrote: How can I ensure that if I add a number of days to a date (which is a working date) the result will also be a work date -- Thanks, Jill |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with working days
In A2 I have the start date, in A3 the number of days to add
This formula add 1 to the answer if the result of A2+A3 would be Sunday, and 2 if the result would be Saturday. Holidays add extra difficulty! (A2+A3) +2*(WEEKDAY(A2+A3)=7)+1*(WEEKDAY(A2+A3)=1) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "JillC" wrote in message ... How can I ensure that if I add a number of days to a date (which is a working date) the result will also be a work date -- Thanks, Jill |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with working days
Give the following function a try...
Function AddWorkDays(StartDate As Date, WorkDays As Long) As Date If WorkDays < 0 Then Exit Function AddWorkDays = DateAdd("d", 7 * (WorkDays \ 5) + (WorkDays Mod 5) - _ 2 * ((WorkDays Mod 5) Abs(5 + - _ Weekday(StartDate, vbMonday))) + _ Weekday(StartDate, vbSaturday) * _ (Weekday(StartDate, vbSaturday) < 3), StartDate) End Function As written, this function will only *add* workdays to the StartDate. If the WorkDays value is less than 0, then "day zero" (12/30/1899) is returned and can be used for error checking purposes. -- Rick (MVP - Excel) "JillC" wrote in message ... How can I ensure that if I add a number of days to a date (which is a working date) the result will also be a work date -- Thanks, Jill |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate working days but change working week | Excel Discussion (Misc queries) | |||
Making weekend days working days - the system cuts the working tim | Excel Discussion (Misc queries) | |||
How to calculation no. of days (only working days) between two dat | Excel Discussion (Misc queries) | |||
Non working days | Excel Discussion (Misc queries) | |||
Working Days | Excel Programming |