Rounding Dates

Good afternoon!

I have a series of job dates that we need to keep track of to ensure they are not overdue. We have a maximum of 6 weeks (42 days) between which visits can be completed, so for example a job completed on the 1st June must be completed by approx 15th July.

This seemed easy at first, I simply used Excel to add 42 days to the original date but there is a problem! Each visit is due monthly and if a job is completed at the end of a month, e.g. 29th June, the next projected date is in August, NOT July.

Is there any way of getting Excel to add 42 days to a date, but for it to know that the next audit date should not be more than one calender month from the last i.e. if last audit is in June, the latest it can be completed is 31st July?

Thanks

Darren

Hi, Darren. If you have the Analysis ToolPak add-in installed, you can use the EOMONTH function:

=MIN(A1+42,EOMONTH(A1,1))

HTH

Jason

Thank you for the quick response - unfortunately I do not have this. Are there any other ways of acheiving the same result?

Thanks

Darren

Hi, Darren. This should work for you:

=MIN(A1+42,DATE(YEAR(A1),MONTH(A1)+2,1)-1)

Jason