Add at least a month to a date.

[migalley]

Hi all,

I have a set of data in Excel, which is sorted with dates. I want to search for the following month's data and compare like data for like. So I start on 5th March, and search and sort up to and including 5th April.

However, if I start on 19th September, and 19th October is a weekend, I won't have any data for 19th October, but the next data may be 21st October.

Is there any way to program this so that Excel VBA can search for and make allowances for missing days (due to weekends or other cause), and go to the next date which is at least 1 month in the future, and stop the loop???

I need this to work over several years, so I can't use a date specific solution. It really should work for any date and 1 month forward.

If any of you have any solution, then I'd appreciate it as it is beyond me.

Thanks!

 

[xlbo]

This should work - mDate is the date at least 1 month on from the input date:

Sub test()
sDate = InputBox("Enter start date - dd/mm/yyyy&quot
thisDay = Format(sDate, "dd&quot
thisMonth = Format(sDate, "mm&quot
thisYear = Format(sDate, "yyyy&quot

Select Case thisMonth
Case 12
nextMonth = 1
nextYear = thisYear + 1
Case Else
nextMonth = thisMonth + 1
nextYear = thisYear
End Select

Select Case Weekday(DateSerial(thisYear, thisMonth, thisDay))
Case 5
nextDay = thisDay + 2
Case 6
nextDay = thisDay + 1
Case Else
nextDay = thisDay
End Select

mDate = DateSerial(nextYear, nextMonth, nextDay)

End Sub

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[migalley]

Hi Geoff,

Thanks for the solution.

I may need to adapt it to the Macro I already have written, so I'll let you know how it goes with an update tomorrow.

Thanks once again!

 

[migalley]

Hi Geoff,

The macro seems to work quite well, but I have found a problem.

If I start with a date of 31 Jan 2000, and want to add a month, there is of course no 31 Feb 2000. So the program jumps from 29 Feb 2000 to 2 Mar 2000. It thinks that 2 Mar 2000 = 31 Feb 2000. Is there any way to make the macro stop at 1 Mar 2000?

What I want is the program to jump to equivalent number in the following month, such as N Sep to N Oct, or to the next available day of the month after that, for example N+1 Oct. If this doesn't exist, I want the program to jump to the first available day of the month after, such as 1 Nov, if it is a weekday.

This may be a tall order, and I'm not sure if Excel VBA can handle it, but if you have any ideas, please let me know.

Thank you for your help.

Gabby.

 

[xlbo]

You just need to put in more "cases" for these
You would need a Case 2
for feb and then test for the day in feb. Have a go at it yourself - all the syntax you should need is there in the code I have given - you just need to adapt if for a few more "special" cases.
If you have a go and can't get it, post back and I'll see what I can do but I'm pretty busy this week - can't promise I'll have a lot of time to figure it out

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[migalley]

Hi Geoff,

I followed your advice, and it works!!!

I hadn't realised that Select Case would be the only way to do this.

Thank you for your help. It was very useful.

Regards,
Gabby

 

[Loomah]

Hi Gabby
Just as another possible idea, for something like your problem, you could try. It uses the 'EDATE' function which is only available if you have the Analysis Toolpak Add-Ins installed. This will give the date one month on unless that is a weekend. In the case of 31 Jan 2000 it will give 29 Feb 2000. If you need to move on to the next month I have added a bit of code that is commented out.

As I said, this is just a slightly different way of approaching the problem for info!

Sub a()
Dim inDate As String
Dim outDate As Date
inDate = InputBox("Enter a Date")
outDate = edate(inDate, 1)
    
    'If edate(outDate, -1) <> inDate Then
    '    outDate = outDate + 1
    'End If

Select Case Weekday(outDate)
    Case 1 'sunday
        outDate = outDate + 1
    Case 7 'saturday
        outDate = outDate + 2
End Select
MsgBox Format(outDate, &quot;ddd, dd mmm yyyy&quot;)
End Sub

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244