Hi,

I need help for Date formula. The scenario is like this:

If the end date of the contract expired 45 days from today, the start date will be today's date.

If the end date of the contract expired less than 45 days, the start date will be the day after the contract end date.

If the end date of the contract expired on today's date, the start date will be the day after the current contract end date.

Thank you for your help.

Let's say the end date is in cell B2.

The formula for the start date is

Format the cell with the formula as a date.

Let's say the end date is in cell B2.

The formula for the start date is

=IF(B2<=TODAY()-45,TODAY(),B2+1)

Format the cell with the formula as a

Hi IsMa,

Based on your description, my understanding is that:

If your work will finish within 1 day, the next start day is the day after the end day;

If your work will take more than 45 days, the next start day is today;

If your work will take less than 45days, the next start day is the day after today.

With these conditions, my formula is:

=IF(B2-A2>45,TODAY(),IF(AND(B2-A2<45,B2-A2<>0),TODAY()+1,B2+1))

I also upload a sample file in OneDrive, you can download it from this link: http://1drv.ms/1e4pI14

Hope it's helpful.

Regards,

Emi Zhang
TechNet Community Su