Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dates in IF statements
I have a small deal I can't figure if I can do... I want to return a value for a date in a specific range of dates, is this possible? TIA, Scott -- ScottEslick ------------------------------------------------------------------------ ScottEslick's Profile: http://www.excelforum.com/member.php...o&userid=30622 View this thread: http://www.excelforum.com/showthread...hreadid=502755 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dates in IF statements
How is the value chosen from the range?
************ Hope it helps! Anne Troy www.OfficeArticles.com "ScottEslick" wrote in message ... I have a small deal I can't figure if I can do... I want to return a value for a date in a specific range of dates, is this possible? TIA, Scott -- ScottEslick ------------------------------------------------------------------------ ScottEslick's Profile: http://www.excelforum.com/member.php...o&userid=30622 View this thread: http://www.excelforum.com/showthread...hreadid=502755 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dates in IF statements
I would like to enter a date in a cell, then if the date falls between a range say Aug 1,1987 to July 31,1988, then return the value U-18. I have ranges from U-4 to U-18. Thanks!!!! Scott -- ScottEslick ------------------------------------------------------------------------ ScottEslick's Profile: http://www.excelforum.com/member.php...o&userid=30622 View this thread: http://www.excelforum.com/showthread...hreadid=502755 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dates in IF statements
If your date is in cell A1, the formula would be:
=IF(AND(A131990,A1<32355),+U18,0) If you type a date in Excel and change the format to "," format, it will give your the date values as above. "ScottEslick" wrote: I would like to enter a date in a cell, then if the date falls between a range say Aug 1,1987 to July 31,1988, then return the value U-18. I have ranges from U-4 to U-18. Thanks!!!! Scott -- ScottEslick ------------------------------------------------------------------------ ScottEslick's Profile: http://www.excelforum.com/member.php...o&userid=30622 View this thread: http://www.excelforum.com/showthread...hreadid=502755 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dates in IF statements
I have ranges from U-4 to U-18.
Does that mean you also have a date range for each U value? If so, then you're best approach would be to build a lookup table. Post back with all the ranges and their corresponding values. Biff "ScottEslick" wrote in message ... I have a small deal I can't figure if I can do... I want to return a value for a date in a specific range of dates, is this possible? TIA, Scott -- ScottEslick ------------------------------------------------------------------------ ScottEslick's Profile: http://www.excelforum.com/member.php...o&userid=30622 View this thread: http://www.excelforum.com/showthread...hreadid=502755 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dates in IF statements
It goes like this: 08/01/87 thru 07/31/88 = U-18 08/01/88 thru 07/31/89 = U-17 08/01/89 thru 07/31/90 = U-16 08/01/90 thru 07/31/91 = U-15 08/01/91 thru 07/31/92 = U-14 08/01/92 thru 07/31/93 = U-13 08/01/93 thru 07/31/94 = U-12 08/01/94 thru 07/31/95 = U-11 08/01/95 thru 07/31/96 = U-10 08/01/96 thru 07/31/97 = U-9 08/01/97 thru 07/31/98 = U-8 08/01/98 thru 07/31/99 = U-7 08/01/99 thru 07/31/00 = U-6 08/01/00 thru 07/31/01 = U-5 08/01/01 thru 07/31/02 = U-4 Thanks for Everyones Help!!!! Scott Biff Wrote: I have ranges from U-4 to U-18. Does that mean you also have a date range for each U value? If so, then you're best approach would be to build a lookup table. Post back with all the ranges and their corresponding values. Biff "ScottEslick" wrote in message ... I have a small deal I can't figure if I can do... I want to return a value for a date in a specific range of dates, is this possible? TIA, Scott -- ScottEslick ------------------------------------------------------------------------ ScottEslick's Profile: http://www.excelforum.com/member.php...o&userid=30622 View this thread: http://www.excelforum.com/showthread...hreadid=502755 -- ScottEslick ------------------------------------------------------------------------ ScottEslick's Profile: http://www.excelforum.com/member.php...o&userid=30622 View this thread: http://www.excelforum.com/showthread...hreadid=502755 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dates in IF statements
Try this formula ="U-"&DATEDIF(A1,DATE(2006,7,31),"y") -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=502755 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dates in IF statements
:) THANKS!!!! That works perfect...this changes every year, can you explain the formula so I can adjust it? Thanks Again, Scott daddylonglegs Wrote: Try this formula ="U-"&DATEDIF(A1,DATE(2006,7,31),"y") -- ScottEslick ------------------------------------------------------------------------ ScottEslick's Profile: http://www.excelforum.com/member.php...o&userid=30622 View this thread: http://www.excelforum.com/showthread...hreadid=502755 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dates in IF statements
=DATEDIF(A1,B1,"y") gives the time difference in years between a date in A1 and a date in B1 so the formula =DATEDIF(A1,DATE(2006,7,31),"y") with a date of birth in A1 gives age at 31st July 2006. For next year change the year to 2007 or put your date in a cell, e.g B1 and use ="U-"&DATEDIF(A1,B1,"y") -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=502755 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
PivotTables - Group Dates, excluding dates | Excel Discussion (Misc queries) | |||
Calculating number of days between two dates that fall between two other dates | Excel Discussion (Misc queries) | |||
if statements & dates? | Excel Worksheet Functions | |||
2 digit year in dates return 19xx not 20xx | Excel Discussion (Misc queries) | |||
Formating Dates for production schedule | Excel Discussion (Misc queries) |