Solved Business Days VBA

[sathsaj]

Dim outputFileName As String
outputFileName = CurrentProject.Path & "\Constituents " & Format(Date - 1, "ddMM") & ".xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Test", outputFileName, True


Above code give me 2101, how can I change this code to get 1901 which Businessdays-1

 

[Gasman]

? Format(Date() - 1, "ddMM") & ".xls"
2101.xls
? date-1
21/01/2024

@Pat Hartman uploaded a DB with a bunch of date functions, but I cannot find it now.

So go to Google

Google Search

 

[theDBguy]

Maybe something like?

outputFileName = CurrentProject.Path & "\Constituents " & Format(Date - IIf(Weekday(Date)=7,3,IIf(Weekday(Date)=,2,1)), "ddMM") & ".xls"

(untested)

Edit: Oops, I got the day numbers wrong. Thanks @Gasman for catching that.

 

[Gasman]

Maybe something like?

outputFileName = CurrentProject.Path & "\Constituents " & Format(Date - IIf(Weekday(Date)=7,3,IIf(Weekday(Date)=6,2,1)), "ddMM") & ".xls"

(untested)

If you go on the default starting day of the week then perhaps

? Format(Date - IIf(Weekday(Date)=2,3,IIf(Weekday(Date)=1,2,1)), "ddMM") & ".xls"

as if Monday (2) then you need to go back 3 days, if Sunday (1) then you go back 2 days,, else just the 1 day?

Do test it all out thoroughly though.

 

[sathsaj]

nothing worked, it still gives me 2101

 

[Gasman]

nothing worked, it still gives me 2101

Really?
Worked fine for me and I changed my computer date to yesterday and Saturday as well as running it for today.

 

[sathsaj]

Really?
Worked fine for me and I changed my computer date to yesterday and Saturday as well as running it for today.

? Format(Date - IIf(Weekday(Date)=2,3,IIf(Weekday(Date)=1,2,1)), "ddMM") & ".xls" - this one worked now, I had some wrong code

 

[Gasman]

It's in the sample databases thread.

Sample Date Functions

This database contains a number of date functions that you can simply copy into your application. There is a tabbed form to show you how to use the calculations. A partial list of functions is: Business days between two dates. Business day + days = next business date Easter (Western) Age...

www.access-programmers.co.uk

Never thought to look there

 

[Gasman]

Too obvious for you

Sadly Yes. I was looking through your posts for it.
Perhaps I will remember next time.