Add 3 Workdays to a Date (1 Viewer)

Ilovexfiles · Jul 7, 2017

Hi all
I have this function below, and I have it as an expression in a query, and I get the error: 'Undefined function 'AddWeekDays' in expression.'

I am new to this, am I missing something?

Expression:
AddWeekDays([AdmitDate],3)
The goal of this expression is to take the "AdmitDate" field and add 3 Weekdays to it, creating a "Due Date"

Function (derived from online):

On Error GoTo PROC_ERR

Dim intCounter As Integer
Dim intDirection As Integer
Dim datNewDate As Date
Dim lngWeeks As Long
Dim intDaysLeft As Integer

datNewDate = datDateIn

If intDays > 0 Then
intDirection = 1
Else
intDirection = -1
End If
lngWeeks = Fix(Abs(intDays) / 5)

If lngWeeks > 0 Then
datNewDate = datNewDate + lngWeeks * 7 * intDirection
End If

intDaysLeft = Abs(intDays) - lngWeeks * 5

For intCounter = 1 To intDaysLeft
datNewDate = datNewDate + 1 * intDirection
If intDirection > 0 Then
' Increment date
If Weekday(datNewDate) = 7 Then
datNewDate = datNewDate + 2
End If
Else
' Decrement date
If Weekday(datNewDate) = 1 Then
datNewDate = datNewDate - 2
End If
End If
Next intCounter

AddWeekDays = datNewDate

PROC_EXIT:
Exit Function

PROC_ERR:
MsgBox "Error: " & Err.Number & ". " & Err.Description, , "modDateTime.AddWeekdays"
Resume PROC_EXIT
End Function

Ilovexfiles · Jul 7, 2017

Nevermind, after using a different code, I changed name of module so as to not match and it worked

'MoveWD moves datThis on by the intInc weekdays.
Public Function MoveWD(datThis As Date, intInc As Integer) As Date
MoveWD = datThis
For intInc = intInc To Sgn(intInc) Step -Sgn(intInc)
MoveWD = MoveWD + Sgn(intInc)
Do While (Weekday(MoveWD) Mod 7) < 2
MoveWD = MoveWD + Sgn(intInc)
Loop
Next intInc
End Function

Orthodox Dave · Jul 7, 2017

I took the easy way out by making a table containing dates for the next 10 years with a boolean yes/no field for non-working days.

Being in the UK, these were Saturdays, Sundays and Public Holidays.

All UK public holidays except Easter can be calculated (e.g. the first and last Mondays in May etc).

I googled the next 10 Easters and added those in (Good Friday and Easter Monday). Other cultures will have different working days and festivals (e.g. Ramadan can also be googled).

A bit of a faff, but at the end you do have a way of adding true working days, which is essential if you are budgeting on a strict cost-time basis.

isladogs · Jul 7, 2017

Hi Dave

If you are interested I have code to calculate when Easter is for any year.
Its not mine but the result of a Google search.
Let me know if useful and i'll upload it

Orthodox Dave · Jul 7, 2017

Thanks Colin, but I'm ok for the next 10 years!
That's pretty cool though, to calculate the first Sunday after the first Full Moon occurring on or after the March equinox. I wonder if it can do the Julian calendar dates too (as followed by the Eastern church).

isladogs · Jul 7, 2017

Oh I'm disappointed in you....

So you don't want to know that in 2099 Easter Sunday is on 12 April !
Thought you might want to start planning!

Anyway in case anyone else does want it here's the function

Code:

Public Function GetEasterSunday(Yr As Integer) As Date

'Code taken from http://www.cpearson.com/excel/Easter.aspx

    Dim D As Integer
    D = (((255 - 11 * (Yr Mod 19)) - 21) Mod 30) + 21
    GetEasterSunday = DateSerial(Yr, 3, 1) + D + (D > 48) + 6 - ((Yr + Yr \ 4 + D + (D > 48) + 1) Mod 7)
            
End Function

Also, whilst I recognised sarcasm in your final comment, attached is a small utility I picked up from somewhere (possibly this site?) but have never found a use for.
It really does do Gregorian / Hebrew / and Islamic holidays!

I don't know the author's name
If anyone recognises it as their work, I'll edit the post

Gasman · Jul 7, 2017

Attachment missing?

ridders said:
Oh I'm disappointed in you....

So you don't want to know that in 2099 Easter Sunday is on 12 April !
Thought you might want to start planning!

Anyway in case anyone else does want it here's the function

Code:

Public Function GetEasterSunday(Yr As Integer) As Date 'Code taken from http://www.cpearson.com/excel/Easter.aspx Dim D As Integer D = (((255 - 11 * (Yr Mod 19)) - 21) Mod 30) + 21 GetEasterSunday = DateSerial(Yr, 3, 1) + D + (D > 48) + 6 - ((Yr + Yr \ 4 + D + (D > 48) + 1) Mod 7) End Function

Also, whilst I recognised sarcasm in your final comment, attached is a small utility I picked up from somewhere (possibly this site?) but have never found a use for.
It really does do Gregorian / Hebrew / and Islamic holidays!

I don't know the author's name
If anyone recognises it as their work, I'll edit the post

The_Doc_Man · Jul 7, 2017

to calculate the first Sunday after the first Full Moon occurring on or after the March equinox.

Wait... did someone just come up with a "phase of the moon" calculator? Oh, darn, I'll have to use a new excuse now for when programs go bad... "It was just the phase of the moon."

isladogs · Jul 7, 2017

Oops - here's the attachment - normally I only do that with emails...

DocMan - now you're being sarcastic as well ....

The_Doc_Man · Jul 7, 2017

Colin, I might be "tongue-in-cheek" sometimes but I wasn't shooting for sarcasm.

Sort of like Haley Joel Osment in "Sixth Sense" ... I see dead jokes.

plog · Jul 7, 2017

I don't think Doc was being sarcastic. And I was thinking the same thing when I first read that you had a function to calculate easter. From wikipedia:

In 325CE the Council of Nicaea established that Easter would be held on the first Sunday after the first full moon occurring on or after the vernal equinox.

Now, I'm off to test your function because I don't believe its possible (especially with code that short) to calculate easter.

isladogs · Jul 7, 2017

Thanks are really due to Chip Pearson....
It really does work - apparently "only" until 2368 - god knows why but she didn't answer my text message.

Clearly I see sarcasm everywhere

And, in case you missed it. I got Colin Essex to say something positive in a Trump thread post (by accident!)

plog · Jul 7, 2017

I tested it for 2000 - 2099 and it worked in every case.

Orthodox Dave · Jul 8, 2017

What have I started?? But before we settle on a permanent solution, please bear this in mind:-

"Pope Francis last year [2015] signalled an openness to changing the date of Easter in the west so that Christians could celebrate it on the same day around the world. The discussions now involve representatives of Francis, the Coptic pope and the ecumenical patriarch of the Orthodox church."
(source https://www.theguardian.com/lifeandstyle/2016/jan/15/easter-justin-welby-christian-attempt-fix-date)

So it could all be changed again within the next, well, 100 years perhaps (so don't hold your breath).

Meanwhile, Colin, you've found a breathtakingly elegant formula for the calculation of Easter which I had thought was impossible. Elegant but very cryptic with its MOD operators and things like "D + (D > 48) + 1" where I presume (D > 48) resolves to 0 or -1. Sheer genius.

plog · Jul 8, 2017

No, the function allows that possibility. It's got a papal_Discretion() sub routine that accounts for that.

isladogs · Jul 8, 2017

I've added the Easter calculator to the code repository to make it easier to find in the future.
If interested Chip Pearson's site also has code to calculate other US based holiday dates such as Thanksgiving.

Talking of Easter, does anyone else remember the flight simulator Easter egg that came with Excel 97.

Apparently Access 97 had an 8 ball game but I missed that one.

Add 3 Workdays to a Date (1 Viewer)

Ilovexfiles

Registered User.

Ilovexfiles

Registered User.

Orthodox Dave

Home Developer

isladogs

MVP / VIP

Orthodox Dave

Home Developer

isladogs

MVP / VIP

Gasman

Enthusiastic Amateur

The_Doc_Man

Immoderate Moderator

isladogs

MVP / VIP

Attachments

The_Doc_Man

Immoderate Moderator

plog

Banishment Pending

isladogs

MVP / VIP

plog

Banishment Pending

Orthodox Dave

Home Developer

plog

Banishment Pending

isladogs

MVP / VIP

Similar threads

Users who are viewing this thread