Highlighted
Respected Contributor.
Respected Contributor.
627 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
Highlighted
Micro Focus Expert
Micro Focus Expert

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)

View solution in original post

0 Likes
4 Replies
Highlighted
Micro Focus Expert
Micro Focus Expert

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
Highlighted
Respected Contributor.
Respected Contributor.

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
Highlighted
Micro Focus Expert
Micro Focus Expert

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)

View solution in original post

0 Likes
Highlighted
Respected Contributor.
Respected Contributor.

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.