Extrapolating the day and or holiday for a given date in a mail merge switch

[Bob789]

Is it possible to extrapolate the day in a mail merge switch for a given date and assign that day a number. In example, given 05/23/2017 from a mail merge field, extrapolate that the day is Tuesday with the value of the day being 3 since Tuesday is the third day of the week. Also is it possible to derive from a date whether or not that day is a bank / national holiday like extrapolating that 05/29/2017 is Memorial Day.

[SamT]

DayNumber = WeekDay(MailMergeField)
WeekDay(05/23/2017) returns 3

For holidays, you need a lookup list since holidays are locale specific

'In Excel VBA
Dim Found as range
Set Found = MyHolidayList.Find("05/23/2017") 'Assumes list is Strings. not Dates
If not Found is Nothing Then
'"05/23/2017" is not a holiday
Else
Holiday = Found.Offset(0, 1)
End if

Note that there is code around the net to compute the random day holidays like Easter.

[Bob789]

Thank you Mark007. So with the holidays it looks like I will have to create a macro. Is the list a table like when using vlookups. Not quite sure how to perform the Dim Found As range. Thank you.

[macropod]

No, you don't need a macro. This can all be done with field coding. For example:
{QUOTE{SET Day {MERGEFIELD DATE \@ "dddd"}}
"{MERGEFIELD DATE \@ "dddd, d MMMM yyyy"} is day {IF{Day}= "Su*" 1 {IF{Day}= "Mo*" 2 {IF{Day}= "Tu*" 3 {IF{Day}= "We*" 4 {IF{Day}= "Th*" 5 {IF{Day}= "Fr*" 6 7}}}}}} of the week."}
It would be helpful, though, if you said what you were trying to achieve. To see how to do just about everything you might want to do with dates in Word, including holiday calculations, check out my Microsoft Word Date Calculation Tutorial, at:
http://windowssecrets.com/forums/showthread.php/154368-Microsoft-Word-Date-Calculation-Tutorial
or:
http://www.gmayor.com/downloads.htm#Third_party
Do read the document's introductory material.

[Bob789]

Paul

I am creating a email merge message to send to my clients that notifies them when their payment has been received and posted to their account. The notification will also give their account balance as of the date of payment. The problem is though that if the payment is past the late date and depending on other variables like even though the payment is late did the late day fall on a weekend and therefore a grace period will apply or does the late balance exceed the minimum balance for late fees etc., the account balance may not include a late and or interest charge and the email merge message may go out with incorrect account balance info. This can happen because the notice goes out before the late and interest charge process is performed in the accounting system. I am therefore trying to create the necessary info in the email merge message to compensate for this possible timing difference. At this point I have actually found a field in my accounting software that I can download into my mail merge spreadsheet that ages balances for me and has now streamlined my approach. The information you have sent is is extremely valuable and useful for my processing. Thank you for your assistance and my guess is I will probably be reaching out to you again.