Excel formula that changes a status value based on a day counter and dates in other cells

[jakecutler]

Hi,

I am trying to get a formula so that a cell (J9) that has a range of statuses will update a Status of "On Track" based on the following:

1) a day counter in cell I9 with at least 7days remaining;

2) if there is at least 7days remaing between a date in cell T9 and a date in cell V9. Eg. If T9 has a date of 20/3/2015 and V9 has a date of 28/3/2015; and

3) and for it to be live updating based on todays date which is in cell T4.

**But the day counter in cell I9 is based on the TODAY() formula so may not need another reference to todays date**

This is beyond my excel skills, i dont even know where to start to be honest!

Any guidance/help would be much appreciated

Thanks

Jake

[azumi]

Not sure about what you after, are you trying calculate distance between 2 dates? In complete years, months and days?

regards

[:) Sixthsense :)]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[jakecutler]

Hi azumi,

Im trying to get excel to calculate the difference between two dates and if there are 7 days or more left then the status will be "On Track"

In addition though, also to get excel to detect how many days are left in a seperate cell with a day counter. So if there are 7 days left on the day counter and 7days or more between the two dates then the status will be "On Track"

cheers

Jake

[jakecutler]

Sample.xlsx

Sample Attached, cells in question i have highlighted in Yellow.

Im assuming its an IF function formula something along the lines of:

=IF(AND(I9>=7,DATEDIFF(T9,V9>=7),"On Track",""

Thanks

Jake

[azumi]

Maybe like this?

=IF(AND(I9>=7,DATEDIF(T9,V9,"d")>=7),"On Track","")

[jakecutler]

Hi Azumi,

Thank you, that has solved it!

Simpler equation required than what i though would be required

Jake