Using Date in formula?

T

Tomo

Board Regular
Joined
Dec 27, 2009
Messages
199
Hi Still having trouble with this one can anyone help?
Excel 2003 and the output is not right have included the basic cell structure

the formula is as follows


=IF(C1="Owner","OK",IF(C1="Not required","Not Required",IF(C1="Required","",IF(TODAY()-C1<90,"OK",IF(TODAY()-C1<60,"REMINDER",IF(TODAY()-C1<30,"BOOK NOW","OVERDUE"))))))

cel a cel b cel c
1 20/10/2013 OK Correct
2 13/03/2014 OK Correct
3 10/11/2013 OK Correct
4 13/03/2014 OK Correct
5 13/03/2014 OK Correct
6 30/01/2012 OK Correct
7 13/03/2014 OK Correct
8 Required Correct
9 Not Required Not Required Correct
10 17/04/2011 OK Should show "Overdue"
11 10/05/2011 OK Should show "Book Now"
12 05/06/2011 OK Should show "Reminder"
13 17/02/2011 OK Should Show "Overdue"
14 17/01/2011 OVERDUE Should Show "Overdue"

Can anyone help please....
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Sort by date Sort by votes
Try:

=IF(ISERROR(MATCH(C1,{"Not Required","Owner","Required"},0)),LOOKUP(Today()-C1,{0,30,60,90},{"BOOK NOW","REMINDER","OK","OVERDUE"}),LOOKUP(C1,{"Not Required","Owner","Required"},{"Not Required","OK",""}))
 
Upvote 0
Hi,

It looks like it is because you have your logic the wrong way round:

Anything that is < 30 days is also less than 90 and 60 days.

Try putting the < 30 days check first, then the 60 then the 90.
 
Upvote 0
Give this a try:

=IF(C1="Owner","OK",IF(C1="Not required","Not Required",IF(C1="Required","",IF(TODAY()-C1<30,"BOOK NOW",IF(TODAY()-C1<60,"REMINDER",IF(TODAY()-C1<90,"OK","OVERDUE"))))))
 
Upvote 0
ok first help resulted in

cells 1-7 *N/A
Cell 8 blank
Cell 9 Not Required
Cell 10 book now
Cels 11-12 *N/A
Cell 13 OK
Cell 14 Overdue


2nd help resulted in

Cells 1-7 Book now
cells 8 blank
cells10-12 Book now
cell 13 ok
cell 14 overdue

it is to tell me when a lgv licence needs to be reapplied for so i can warn people when theres are running out.
 
Upvote 0
I think its because subtracting the later dates from today is resulting in a negative number. Try
=IF(C1="Owner","OK",IF(C1="Not required","Not Required",IF(C1="Required","",IF(ABS(TODAY()-C1)<30,"BOOK NOW",IF(ABS(TODAY()-C1)<60,"REMINDER",IF(ABS(TODAY()-C1)<90,"OK","OVERDUE"))))))
 
Upvote 0
So frustrating the first 6 cells should read ok because they are way in the futureusing todays date you see they are not right

<TABLE style="WIDTH: 153pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=203><COLGROUP><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 91pt; mso-width-source: userset; mso-width-alt: 4425; mso-outline-level: 1" width=121><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 62pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2689128 class=xl71 height=17 width=82>cell a</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 91pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 width=121>cel b</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 height=17>20/10/2013</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72>OVERDUE

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 height=17>13/03/2014</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72>OVERDUE</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 height=17>10/11/2013</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72>OVERDUE</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 height=17>13/03/2014</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72>OVERDUE</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 height=17>13/03/2014</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72>OVERDUE</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 height=17>30/01/2012</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72>OVERDUE</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 height=17>13/03/2014</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72>OVERDUE</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=17>Required</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 height=17>Not Required</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72>Not Required</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 height=17>17/04/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72>BOOK NOW</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 height=17>10/05/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72>BOOK NOW</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 height=17>05/06/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72>REMINDER</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 height=17>17/02/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72>OK</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 height=17>17/01/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72>OVERDUE</TD></TR></TBODY></TABLE>
 
Upvote 0
Hello Tomo,

assuming data in A2 try this formula

=IF(OR(A2={"","Not Required"}),A2,IF(A2="Owner","OK",IF(A2="Required","",LOOKUP(A2-TODAY(),{-9999,0,30,60;"Overdue","Book Now","Reminder","OK"}))))
 
Upvote 0
Gotcha. the less than / greater than signs have been backward the whole time.

=IF(C1="Owner","OK",IF(C1="Not required","Not Required",IF(C1="Required","",IF(ABS(TODAY()-C1)>90,"OK",IF(ABS(TODAY()-C1)>60,"REMINDER",IF(ABS(TODAY()-C1)>30,"BOOK NOW","OVERDUE"))))))
 
Upvote 0
Mr Houdini

You pulled it out of the bag well done,
Thanks everyone else who helped it has been a nightmare to fix.

I will sleep well tonight...:)
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Help Please
2 3
Replies
21
Views
513
Asbestos_Jen
Asbestos_Jen
SaraWitch
  • Solved
Overdue date
Replies
4
Views
861
etaf
etaf
S
  • Solved
How to Highlight Month-end dates
Replies
3
Views
613
kevin9999
K
R
  • Solved
Age Group Ranging by gender
Replies
2
Views
273
Raj1979
R
SFCChase
  • Question Question
Use Dropdown to search other tabs
Replies
4
Views
163
SFCChase
SFCChase

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top