Excel Dates - Make a date the 1st day of the following month

[meierswa]

I am trying to determine the number of business days between two dates; however I need to make the start date the first day of the following month.

I am using the following formula to determine the number of business days:

=NETWORKDAYS(C10,Q10)-1

How can I make the start date (cell C10) the first day of the following month?

 

[bluedragon2]

say date is in cell A1:

=IF(MONTH(A1)=12,DATE(YEAR(A1),1,1),DATE(YEAR(A1),MONTH(A1)+1,1))

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[bluedragon2]

say date is in cell A1:

=IF(MONTH(A1)=12,DATE(YEAR(A1)+1,1,1),DATE(YEAR(A1),MONTH(A1)+1,1))

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[Rofeu]

Hi,

you don't need to compensate for exceeding the year.

=DATE(YEAR(A1),MONTH(A1)+1,1)

Say date is 01/12/2006, Excel will automatically increment the year to 2007.

Cheers,

Roel

 

[AnotherHiggins]

Even easier still:

[COLOR=blue white]=EOMonth(Today(),0)+1[/color]

The formuala returns the last day of a month (EndOfMonth). Adding 1 retuns the first day of the following month.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[AnotherHiggins]

By the way, if you haven't already done so, you will need to enable the Analysis Toolpak to use the EOMonth function.

Tools > Add-Ins > Analysis ToolPak > OK

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[bluedragon2]

Thanks guys, wasn't sure about the end of year, had no time to test it

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[meierswa]

EndOfMonth coupled with NetworkDays and an If statement did the trick.

Thanks for the help.