Highlighted
ErikAllen Respected Contributor.
Respected Contributor.
353 views

Formatting Date Question

Jump to solution

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!

Tags (2)
0 Likes
1 Solution

Accepted Solutions
Micro Focus Contributor
Micro Focus Contributor

Re: Formatting Date Question

Jump to solution

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)

0 Likes
4 Replies
Micro Focus Contributor
Micro Focus Contributor

Re: Formatting Date Question

Jump to solution

Simple:

 

d = Date - 30 ' will remove 30 days

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

 

Thanks

Adi

0 Likes
ErikAllen Respected Contributor.
Respected Contributor.

Re: Formatting Date Question

Jump to solution

Adia, 

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

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

0 Likes
Micro Focus Contributor
Micro Focus Contributor

Re: Formatting Date Question

Jump to solution

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)

0 Likes
ErikAllen Respected Contributor.
Respected Contributor.

Re: Formatting Date Question

Jump to solution

Adia,

Thank you! That is great!

 

Only thing I need to figure out now, is how to impliment this into my current scripts.

I'll see if it works the way I think it's going to and get back to you. 

 

Thank you again!

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.