Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trying to create an overdue message based on set dates
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trying to create an overdue message based on set dates
On 22 Nov, 15:50, Craig wrote:
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 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trying to create an overdue message based on set dates
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trying to create an overdue message based on set dates
Thanks guys
"Herbert Seidenberg" wrote: 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trying to create an overdue message based on set dates
One quick question.
Where do i add the message "Overdue" in the formula you gave me? "Herbert Seidenberg" wrote: 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trying to create an overdue message based on set dates
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") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Due Dates / Overdue items | Excel Discussion (Misc queries) | |||
countif non blank cells + dates overdue | Excel Worksheet Functions | |||
CREATE A SUM BASED ON DATES | Excel Worksheet Functions | |||
conditional formatting overdue dates | Excel Discussion (Misc queries) | |||
conditional formatting overdue dates | Excel Discussion (Misc queries) |