Calculating the prior month given the current month

**AnthonyWB** · 04-15-2010, 11:58 AM

Hello everyone. I am currently coding a project which requires information from a prior month. What I have is a message box which prompts the user to enter the date: dd-mmm-YYYY format. That value is then stored as a variable deontoed fDate. I would like to create a sub macro which takes fDate and"subtracts a month" and create a file which will be named after that, for instance if the current month is 30-jan-2010, then a file will be created and named 31-dec-2009. My line of thought was "fdate -1", but that returns an error. Please see the code below, specificaly Sub CreateFiles (). Any help is greatly appreciated.

Please Login or Register  to view this content.

**Marcol** · 04-15-2010, 12:38 PM

One way to move the date back by one month with VBa

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

The above works for up to the 30th of any month except February.

There are probably smarter ways, just can't think just now.

Cheers

What do you want to do if the date is 31/03/2010 ?

**AnthonyWB** · 04-15-2010, 01:27 PM

I cannot quite follow your line of thought. Should I do this in a "hidden sheet" and save the prior-month-calculation and use that?

**Marcol** · 04-15-2010, 01:55 PM

Sorry only a demo, I should have said so.

No just change Range("A1") to your date variable fDate

Therefore

Please Login or Register  to view this content.

Please read my comments in Post #2 again

Do you want to use the last date in any month rather than just moving the date back by one month?

Cheers

**JBeaucaire** · 04-15-2010, 02:08 PM

Originally Posted by Marcol

Please Login or Register  to view this content.

Anthony, I noticed in your original example you wanted to go from the fDate manually entered (30-JAN-2010) to the end of the month prior (31-DEC-1999).

For that, you would probably have to convert whatever fDate value was given back to the first day of the current month, then subtract 1 day. Then you would need to convert it BACK to a string...

Please Login or Register  to view this content.

**AnthonyWB** · 04-15-2010, 02:11 PM

I don't want to change fDate, as this variable is used elsewhere in the code and I need to preserve the original value that was input by the user. I believe what I need is an additional variable say pastDate (As String ?), and set

pastDate = (fDate,-1), or somthing like this. The date format will be dd-mmm-YYYY, for instance 31-dec-2009.

**AnthonyWB** · 04-15-2010, 02:15 PM

JBeaucaire ,

Is it possible for me to set

Please Login or Register  to view this content.

where pastDate is declared as a string publicaly?

**AnthonyWB** · 04-15-2010, 02:25 PM

By using the formula you provided, JBeaucaire I reieve an error. By using the Intermediate window to assist with debuggins I also notice that pastDate is set to the year prior. For instance if I enter 31-dec-2009 then pastDate = 12/30/2008. Furthermore I need pastDate to have the same format as fDate. Then it will work.

Please Login or Register  to view this content.

**broro183** · 04-15-2010, 02:26 PM

hi all,

I'm too slow at posting, but since I've scribbled it up & just to throw another variation into the mix...
(very similar to those given, but with the new example of "day zero" & DateAdd)

Please Login or Register  to view this content.

hth
Rob

**Marcol** · 04-15-2010, 02:28 PM

To get the last date in the month previous to the month in fDate

Please Login or Register  to view this content.

Cheers

**AnthonyWB** · 04-15-2010, 02:39 PM

We are almost there it is just a matter of formatting:

Please Login or Register  to view this content.

fDatePrevious is stored as MM/DD/YYYY. I need dd-mmm-yyyy.

**AnthonyWB** · 04-15-2010, 02:51 PM

Here is what I have come up with. Both fDatePrevious and fPriorDate are declared publically.

Please Login or Register  to view this content.

where I am setting

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

The code now executes, but I when i check the value of fPriorDate in the intermediate window, its value is empty?

**AnthonyWB** · 04-15-2010, 03:11 PM

I got it, thank you very much everyone.

Please Login or Register  to view this content.

**broro183** · 04-15-2010, 04:23 PM

Cool, I'm pleased we could help

Thanks for marking the thread as Solved.

Rob

**JBeaucaire** · 04-15-2010, 04:40 PM

Cool, I was about to start another pot of coffee!

Go Team.

Calculating the prior month given the current month

LinkBack

Thread Tools

Rate This Thread

Display

Calculating the prior month given the current month

Re: Calculating the prior month given the current month

Re: Calculating the prior month given the current month

Re: Calculating the prior month given the current month

Re: Calculating the prior month given the current month

Re: Calculating the prior month given the current month

Re: Calculating the prior month given the current month

Re: Calculating the prior month given the current month

Re: Calculating the prior month given the current month

Re: Calculating the prior month given the current month

Re: Calculating the prior month given the current month

Re: Calculating the prior month given the current month

Re: Calculating the prior month given the current month

Re: Calculating the prior month given the current month

Re: Calculating the prior month given the current month

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions