Format a Date

[prasadgov]

Hi,

I have a string which reads the date as 20240730

"C\backup\LReports" & "\Output\" & sFolderLocation & "\" & "Report_" & sDateTimeStamp & ".xlsm"

sFolderLocation = CStr(Year(Now())) &
Pad(CStr(Month(Now())), 2)


sDateTimeStamp = CStr(Year(Now())) & _
Pad(CStr(Month(Now())), 2) & _
Pad(CStr(Day(Now())), 2)

If I need to read the 07/29(the day before) or on Mondays, if I need Friday's date (20240726) file, how to use that in my variable?

 

[cheekybuddha]

You are using an overly complex method to create your date string. You can use:

' ...
sDateTimeStamp = Format(Date, "yyyymmdd")
' ...

For yesterday:

' ...
sDateTimeStamp = Format(Date - 1, "yyyymmdd")
' ...

I'll give you a chance to try and work out how to get the previous Friday.

 

[cheekybuddha]

i would do your code in the opposite order.

If yesterday or last Friday are in the previous month you might file your report in the wrong folder.

I'm writing from my phone so this is aircode:

Dim sFolderLocation As String
Dim sDateTimeStamp As String
Dim dt As Date

dt = Date - 1
Select Case Weekday(dt)
Case vbSunday
  dt = dt - 2
Case vbSaturday
  dt = dt - 1
End Select
sFolderLocation = Format(dt, "yyyymm")
sDateTimeStamp = Format(dt, "yyyymmdd")

"C\backup\LReports" & "\Output\" & sFolderLocation & "\" & "Report_" & sDateTimeStamp & ".xlsm"