Help Setting Date Format in VBA to YYYYMMDD

[watercooled81]

Hi

I need help with setting a date format. I have a number of reports that come in with a file name YYYYMMDD - Unweighted.xls
So I wrote the following code (I am very new to VBA) and thought I was on to a winner, but then during testing I realised 'ThisMondate' was just a number ie 20110704 and if I take away 7 i get 20110697 rather then 20110627 the previous Monday I wanted. I have had a look online, but can not find any solutions... Any help would be greatly appriceated.
Thank You

The code and questions was to get around we are sometimes closed on a Monday and the reports come in Tuesday.

Dim ThisMon As String
Dim SaleFile As String
Dim PrevSaleFile As String


ThisMon = MsgBox("Was this monday a working day?", vbYesNo)
ThisMondate = InputBox("Please enter Monday's date in the format YYYYMMDD. ie. 20110725")
PrevMonDate = MsgBox("Was last monday a working day.", vbYesNo)

Select Case ThisMon

Case vbYes

SaleFile = ThisMondate

Case vbNo

SaleFile = ThisMondate + 1

End Select

Select Case PrevMonDate

Case vbYes

PrevSaleFile = ThisMondate - 7

Case vbNo

PrevSaleFile = ThisMoDate - 6

End Select

'The VBA then goes on to open the various files and copy certain information... I am ok with this bit'

ChDir "S:\Reports"
Workbooks.Open Filename:= _
"S:\Reports\" & SaleFile & " - Unweighted.xls"
Sheets("Summary").Select

 

[PHV]

A starting point:

...
Dim theDate As Date
theDate=DateSerial(Left(ThisMonDate,4),Mid(ThisMonDate,5,2),Right(ThisMonDate,2))
Select Case ThisMon
Case vbYes
  SaleFile = Format(theDate,"yyyymmdd")
Case vbNo
  SaleFile = Format(theDate+1,"yyyymmdd")
End Select
Select Case PrevMonDate
Case vbYes
  PrevSaleFile = Format(theDate-7,"yyyymmdd")
Case vbNo
  PrevSaleFile = Format(theDate-6,"yyyymmdd")
End Select
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[watercooled81]

Works a treat..... Thank you so much!!!