Date look up/date due equation question

F

fiftimedun

New Member
Joined
Oct 13, 2009
Messages
47
Opening note: I would like to do this without VBA if possible, I don't know or understand code and I like to know and understand what I am putting in my excel sheets.

We have about 25 trucks that need washed every 3 weeks for corrosion control. I want to make an equation that will bring up what truck(s) are due to be washed that day.

My idea so far has been to have a separate tab with a list of trucks and dates and use a type of hlookup or vlookup function. Any thoughts? Thanks for your input/assistance!
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
Instead of a separate tab, you could use Conditional Formatting to highlight the trucks due for a wash.

Example:
Excel Workbook
AB
1TruckLast Wash
2Truck17/30/2011
3Truck27/31/2011
4Truck37/21/2011
5Truck47/22/2011
6Truck58/3/2011
7Truck67/28/2011
8Truck77/29/2011
Sheet
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A21. / Formula is =$B2+21<=TODAY()Abc
B21. / Formula is =$B2+21<=TODAY()Abc
A31. / Formula is =$B3+21<=TODAY()Abc
B31. / Formula is =$B3+21<=TODAY()Abc
A41. / Formula is =$B4+21<=TODAY()Abc
B41. / Formula is =$B4+21<=TODAY()Abc
A51. / Formula is =$B5+21<=TODAY()Abc
B51. / Formula is =$B5+21<=TODAY()Abc
A61. / Formula is =$B6+21<=TODAY()Abc
B61. / Formula is =$B6+21<=TODAY()Abc
A71. / Formula is =$B7+21<=TODAY()Abc
B71. / Formula is =$B7+21<=TODAY()Abc
A81. / Formula is =$B8+21<=TODAY()Abc
B81. / Formula is =$B8+21<=TODAY()Abc
 
Upvote 0
Slightly different approach:
1- Check to see if the next 21 days falls on weekends or not
2- Highlight all scheduled washing for the current month

Hope this helps..

Excel Workbook
ABCDEF
2TrucksLast date washedSchedule***
3Truck -108/05/1108/27/1109/17/1110/08/1110/29/11
4Truck -208/10/1108/31/1109/21/1110/12/1111/02/11
5Truck -307/18/1108/08/1108/29/1109/19/1110/10/11
6Truck -407/17/1108/07/1108/28/1109/18/1110/09/11
7Truck -506/28/1107/19/1108/09/1108/30/1109/20/11
8Truck -608/14/1109/04/1109/25/1110/16/1111/06/11
9Truck -707/13/1108/03/1108/24/1109/14/1110/05/11
10Truck -808/06/1108/27/1109/17/1110/08/1110/29/11
11Truck -907/15/1108/06/1108/27/1109/17/1110/08/11
12Truck -1008/10/1108/31/1109/21/1110/12/1111/02/11
13Truck -1108/25/1109/15/1110/06/1110/27/1111/17/11
14Truck -1207/25/1108/15/1109/05/1109/26/1110/17/11
15Truck -1307/12/1108/02/1108/23/1109/13/1110/04/11
16Truck -1408/04/1108/25/1109/15/1110/06/1110/27/11
17Truck -1507/14/1108/04/1108/25/1109/15/1110/06/11
18Truck -1608/02/1108/23/1109/13/1110/04/1110/25/11
19Truck -1707/17/1108/07/1108/28/1109/18/1110/09/11
20Truck -1807/11/1108/01/1108/22/1109/12/1110/03/11
21Truck -1907/04/1107/25/1108/15/1109/05/1109/26/11
22Truck -2007/26/1108/16/1109/06/1109/27/1110/18/11
23Truck -2108/16/1109/06/1109/27/1110/18/1111/08/11
24Truck -2208/01/1108/22/1109/12/1110/03/1110/24/11
25Truck -2306/28/1107/19/1108/09/1108/30/1109/20/11
26Truck -2408/16/1109/06/1109/27/1110/18/1111/08/11
27Truck -2507/20/1108/10/1108/31/1109/21/1110/12/11
Sheet5
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C31. / Formula is =C3<=TODAY()Abc
C32. / Formula is =MONTH(C3)=MONTH(TODAY())Abc


You can then update the "The last washed" column as tasks been performed and have the new schedule wash and so on .
 
Upvote 0
The info needs to be on a sheet that has a lot more info so the seperate tab wont work (it's a printed page) Here is what i did, i created a separate tab, i'll call it "data" here and i listed about 15 due dates

(well into 2012, so this will last for a while)

My main question now... i was wondering if there is any way to do it w/o having such a huge "if" statement. :eeek:(seriously it's like 20 or so nested if's)

As it stands here's a SMALL example:

Thanks again for any and all input and help :)

Code: 
=IF('data'!A1=TODAY(),"Wash Today",IF('data'!B1=TODAY(),"Wash Today",IF('data'!C1=TODAY(),"Wash Today",IF('data'!D1=TODAY(),"Wash Today","Not due for wash"))))
data

<table style="font-family: Calibri,Arial; font-size: 11pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 64px;"><col style="width: 68px;"><col style="width: 68px;"><col style="width: 64px;"><col style="width: 68px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td><td>Truck1</td><td style="text-align: right;">8/23/2011</td><td style="text-align: right;">8/30/2011</td><td style="text-align: right;">9/6/2011</td><td style="text-align: right;">9/13/2011</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td><td>Truck2</td><td style="text-align: right;">8/24/2011</td><td style="text-align: right;">8/31/2011</td><td style="text-align: right;">9/7/2011</td><td style="text-align: right;">9/14/2011</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td>Truck3</td><td style="text-align: right;">8/25/2011</td><td style="text-align: right;">9/1/2011</td><td style="text-align: right;">9/8/2011</td><td style="text-align: right;">9/15/2011</td></tr></tbody></table>



Wash

<table style="font-family: Calibri,Arial; font-size: 11pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td>
</td><td>A</td><td>B</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td><td>Truck1</td><td>Wash Today</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td><td>Truck2</td><td>Not due for wash</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td>Truck3</td><td>Not due for wash</td></tr></tbody></table>
<table style="font-family: Arial; font-size: 10pt; border-style: groove; border-color: rgb(0, 255, 0); background-color: rgb(255, 252, 249); color: rgb(0, 0, 0);"><tbody><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family: Arial; font-size: 9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color: rgb(202, 202, 202); font-size: 10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>B1</td><td>=IF(data!B1=TODAY(),"Wash Today",IF(data!C1=TODAY(),"Wash Today",IF(data!D1=TODAY(),"Wash Today",IF(data!E1=TODAY(),"Wash Today","Not due for wash"))))</td></tr><tr><td>B2</td><td>=IF(data!B2=TODAY(),"Wash Today",IF(data!C2=TODAY(),"Wash Today",IF(data!D2=TODAY(),"Wash Today",IF(data!E2=TODAY(),"Wash Today","Not due for wash"))))</td></tr><tr><td>B3</td><td>=IF(data!B3=TODAY(),"Wash Today",IF(data!C3=TODAY(),"Wash Today",IF(data!D3=TODAY(),"Wash Today",IF(data!E3=TODAY(),"Wash Today","Not due for wash"))))</td></tr></tbody></table></td></tr></tbody></table>
 
Upvote 0
Try this...

B1
=IF(ISNUMBER(MATCH(TODAY(), Data!$A1:$D1,0)), "Wash Today", "Not due for wash")

It uses the Match function to test if Today's date is matched in range Data!$A1:$D1


Or this...
=IF(COUNTIF(Data!$A1:$D1, TODAY()), "Wash Today", "Not due for wash")
 
Last edited:
Upvote 0
AlphaFrog said:
...
=IF(COUNTIF(Data!$A1:$D1, TODAY()), "Wash Today", "Not due for wash")
Click to expand...

This is the simplest most awesome thing ever! thank you so much, here's what my if statement WAS:

=IF('Wash dates'!B3=TODAY(),"<--WASH TODAY!",IF('Wash dates'!C3=TODAY(),"<--WASH TODAY!",IF('Wash dates'!D3=TODAY(),"<--WASH TODAY!",IF('Wash dates'!E3=TODAY(),"<--WASH TODAY!",IF('Wash dates'!F3=TODAY(),"<--WASH TODAY!",IF('Wash dates'!G3=TODAY(),"<--WASH TODAY!",IF('Wash dates'!H3=TODAY(),"<--WASH TODAY!",IF('Wash dates'!I3=TODAY(),"<--WASH TODAY!",IF('Wash dates'!J3=TODAY(),"<--WASH TODAY!",IF('Wash dates'!K3=TODAY(),"<--WASH TODAY!",IF('Wash dates'!L3=TODAY(),"<--WASH TODAY!",IF('Wash dates'!M3=TODAY(),"<--WASH TODAY!",IF('Wash dates'!N3=TODAY(),"<--WASH TODAY!",IF('Wash dates'!O3=TODAY(),"<--WASH TODAY!",IF('Wash dates'!P3=TODAY(),"<--WASH TODAY!",IF('Wash dates'!Q3=TODAY(),"<--WASH TODAY!",IF('Wash dates'!R3=TODAY(),"<--WASH TODAY!",IF('Wash dates'!S3=TODAY(),"<--WASH TODAY!","No Wash"))))))))))))))))))
Click to expand...

THANKS AGAIN!!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Question Question
Need formula to distinguish is task date is due within the month
Replies
1
Views
343
etaf
etaf
S
  • Question Question
Macro to add sum total to track percentages
Replies
0
Views
337
spenserallen7
S
J
  • Question Question
Calculation based on value in table based on date range
Replies
0
Views
275
jsand
J
G
  • Question Question
Using a set date to prevent access to Excell tabs and macros
Replies
1
Views
231
Micron
M
N
  • Question Question
Formula help to search a table for a date greater than today that has a non-blank cell and return the date in a separate cell.
Replies
2
Views
542
Alex Blakenburg
A

Forum statistics

Threads
1,224,607
Messages
6,179,871
Members
452,948
Latest member
UsmanAli786

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