Hello all,

I  use the following line to format my date to this format: 04/03/2019.

Format(Month(Date), "00") & "/" & Format(day(Date), "00") & "/" & Format(Year(Date), "0000")

While that works fine, my issue is when I minus days from the Day section, if it's too many days back that goto the previous month, it won't display the previous month.

Example: If today is 04/03/2019

Format(Month(Date), "00") & "/" & Format(day(Date)-3, "00") & "/" & Format(Year(Date), "0000") 

Gives me 04/00/2019 when I need it to refer to 03/31/2019.

Can anyone give me some advice on how to accomplish this?

Thank you in advance!

Simple:

d = Date - 30 ' will remove 30 days

myDate = Format(Month(d), "00") & "/" & Format(Day(d), "00") & "/" & Format(Year(d), "0000")

Thanks

Adi

Adia, 

Thank you for the reply, but could you elaborate a little further?

I keep getting an mismatch error when trying to use this.

Hi Erik,

Sorry I thought you are using new Rumba with latest VBA support which I recommend to move to...

Here is updated code that will work over old VBA engine

The trick here is to use DateSerial which can handle the month / years shift if needed

In my case I reduced 30 days from today 30/04/2019 and got 31/03/2019

d = Date
d = DateSerial(Year(d), Month(d), Day(d) -30)
myDate = Format(Month(d), "00") & "/" & Format(Day(d), "00") & "/" & Format(Year(d), "0000")
Msgbox(myDate)