Excel date conversions

[razorback]

Is there an excel fomula to automatically change a date to the beginning of the next month? Example: A date of 2-16-07 (in one cell) would be converted into 3-1-07 (in another cell).

[2-1-07 would remain 2-1-07]

Thanks ...

 

[Shamsuddeen]

If your Date is in Cell A1, put the following formula in B1:

=IF(DAY(A1)<>1,DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)-DAY(A1)+1),A1)


Thanks
Shamsuddeen

 

[Scott Huish]

Hi, welcome to the board!

=IF(DAY(A1)>1,DATE(YEAR(A1),MONTH(A1)+1,1),A1)

or if you have the Analysis Toolpak loaded:
=IF(DAY(A1)>1,EOMONTH(A1,0)+1,A1)
Format as date.

 

[Datsmart]

Try this formula:

=IF(DAY(A1)>1,DATE(YEAR(A1),MONTH(A1)+1,1),DATE(YEAR(A1),MONTH(A1),1))

Assumes your date is in cell A1. Format cell as date.

 

[barry houdini]

With Analysis ToolPak

=EOMONTH(A1-1,0)+1

or without

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