Dates

[snapper1969]

I have a date in a cell in excel formatted as dd/mm/yyyy. I need to calculate 2 working days before that date and insert in another cell. The working days are Mon - Fri. Mon and Tue would only be the problem days. Is this something MS can do?.

Thx,

John

[Marcus Tettmar]

Here's a function that should do the job:

Code: Select all

VBSTART
Function GetWorkingDay(thedate,numdaysbefore)
  'subtract 2 days from the date
  thedate = DateAdd("d",-2,thedate)
  MsgBox thedate

  'if result is a Sunday, subract 2 days
  if WeekDay(thedate) = 1 then
    thedate = DateAdd("d",-2,thedate)
  end if

  'if result is a Saturday, subract 1 day
  if WeekDay(thedate) = 7 then
    thedate = DateAdd("d",-1,thedate)
  end if

  GetWorkingDay = thedate

End Function
VBEND

//example - should return 31st March
VBEval>GetWorkingDay("04/02/2008",2),newdate

[Me_again]

That code returns Friday for Monday, it may be open to interpretation but I think it should subtract 2 days for both the Sunday and Saturday result:

[code]
VBSTART
Function GetWorkingDay(thedate,numdaysbefore)
'subtract 2 days from the date
thedate = DateAdd("d",-2,thedate)
MsgBox thedate

'if result is a Sunday, or Saturday subtract 2 days
if WeekDay(thedate) = 1 or 7 then
thedate = DateAdd("d",-2,thedate)
end if

GetWorkingDay = thedate

End Function
VBEND

//example - should return Thursday 4/3
VBEval>GetWorkingDay("04/07/2008",2),newdate
MDL>New Date is: %newdate%
[/code]