Trying to create an overdue message based on set dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a simple grid spreadsheet with a list of road names down the left and
1st cut, 2nd cut, 3rd cut etc along the top. The idea is to show how many
times the grass in each road is cut. There should be one cut every two weeks
and the date of that cut is then entered. What i would like to do is create a
formula whereby all the roads against 1st cut automatically display the
message 'overdue' if they haven't been visited within two weeks of the start
date (the start date could be 1st January for instance). The 2nd cut should
be four weeks from the start, 3rd cut six weeks so on and so on.

Any help would be greatly appreciated

Thanks
Craig
 
I have a simple grid spreadsheet with a list of road names down the left and
1st cut, 2nd cut, 3rd cut etc along the top. The idea is to show how many
times the grass in each road is cut. There should be one cut every two weeks
and the date of that cut is then entered. What i would like to do is create a
formula whereby all the roads against 1st cut automatically display the
message 'overdue' if they haven't been visited within two weeks of the start
date (the start date could be 1st January for instance). The 2nd cut should
be four weeks from the start, 3rd cut six weeks so on and so on.

Any help would be greatly appreciated

Thanks
Craig
Click to expand...

Have you tried using the conditional formatting function in the cell
format menu? This would allow you for example to make the cell turn
red if the date specified in the cell was beyond todays date.
 
I second Suleman's suggestion.
If you have these dates, starting at A1
1/1/2007 1/14/2007 1/29/2007 2/13/2007
enter this conditional format formula for A2:A4
=DATEDIF($A$1,B$1,"d")>(COLUMN()-1)*14
Cell D3 should turn red.
 
If you prefer this format:
2/2/2007 2/15/2007 2/28/2007 3/17/2007
OK OK Overdue
then put this formula into B2 and copy to the right:
=IF((B1-$A1)>(COLUMN()-1)*14,"Overdue","OK")
 
Back
Top