formula to calculate dates! HELP!

I consider myself a pretty advanced Excel user, I've even programmed in VB to create some cool macros, but the following problem stumps me.

I have an internal client who gave me a spreadsheet with over 3,000 employee names and their start and termination dates on it.

She asked me to figure out what the next 15th or end of the month would be after the termination date for the people who are no longer employed. There are hundreds of people so I'm NOT going to do this manually.

Is there a formula you can think of that would do this?

I am drawing a blank!

I'm not very good with calculating dates in Excel!!! PLEASE HELP!

hermie2000,

Try,

If you are looking to retrieve the date that is the same month as the term month.

For the 15th

=DATE(YEAR(B2),MONTH(B2),15)

For the end of month date

=EOMONTH(B2,0))

If you want to get the following month's data then modify to,

=DATE(YEAR(B2),MONTH(B2)+1,15)

&

=EOMONTH(B2,1))

You can incorporate these into an IF statement to accomodate any criteria you may need to apply. i.e. If term date is less than the 15th of the month you want the 15th and if greater than the 15th you want the end of month.

=IF(DAY(B2)<15,DATE(YEAR(B2),MONTH(B2)+1,15),EOMONTH(B2,1))

The EOMONTH function requires the Analysis ToolPak to be installed. Tools>Addins. Check Analysis Toolpak & OK.



HTH

Steve