Calculate 1st of month date from existing date.

[Jim15]

I want Excel to post a starting date which is 45 days (or other time
period) from an initial date. The rules are: (1) If the date is under
15 (middle of the month) set the starting date to the 1st of the month
as calculated and (2) if the date is 15 or over, set starting date to
the 1st of the next month.

For instance, if my starting date is January 1st, 2006 and I want the
date 45 days from there, the answer is February 15, 2006. Reset the
calculated value of February 15, 2006 to February 1, 2006. If the date
was February 16, 2006 (46 days after start), set the date to March 1,
2006.

Thanks,

Jim15

 

[Ron Rosenfeld]

I want Excel to post a starting date which is 45 days (or other time
period) from an initial date. The rules are: (1) If the date is under
15 (middle of the month) set the starting date to the 1st of the month
as calculated and (2) if the date is 15 or over, set starting date to
the 1st of the next month.

For instance, if my starting date is January 1st, 2006 and I want the
date 45 days from there, the answer is February 15, 2006. Reset the
calculated value of February 15, 2006 to February 1, 2006. If the date
was February 16, 2006 (46 days after start), set the date to March 1,
2006.

Thanks,

Jim15

=StartDate+45+17*(DAY(StartDate+45)>15)-
DAY(StartDate+45+17*(DAY(StartDate+45)>15))+1


--ron