Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formulas to count dates
I have input the following dates into cells A1,B1,C1,D1 respectively...March
30, 2009, August 20, 2009, July 30, 2008, November 20, 2008. Cell Z1 is todays date =TODAY() which is August 27. I would like three formulas as follows: 1) Cell e1 that will count the number of days following the most recent date of the 4 cells above, in this example the result is "7". 2) Cell f1 that will show a date that is 3 days after the most recent date of the 4 cells, in this example the result is August 30/09. 3) Cell g1 that will count days based on two criteria: if all of the 4 cells (a1-d1) are dates in the past, then g1 will add 30 days to the most recent date and count the number of days from today to that date or if one of the dates in the 4 cells is a date in the future, then the value in g1 is simply the number of days from today to that date, in this example the result is "23". Thanks very much for your help with this. -- Brian |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formulas to count dates
I think these are what you want...
1) =TODAY()-MAX(A1:D1) 2) =MAX(A1:D1)+3 3) =IF(MAX(A1:D1)<TODAY(),TODAY()-MAX(A1:D1),MAX(A1:D1)+30-TODAY()) Note: Number 1) above assumes all the dates are in the past. If not, you will need something like this... =IF(MAX(A1:D1)<TODAY(),TODAY()-MAX(A1:D1),"???") where you would put whatever you want in place of the ??? to signal the maximum date is a later date than today. -- Rick (MVP - Excel) "Brian" wrote in message ... I have input the following dates into cells A1,B1,C1,D1 respectively...March 30, 2009, August 20, 2009, July 30, 2008, November 20, 2008. Cell Z1 is todays date =TODAY() which is August 27. I would like three formulas as follows: 1) Cell e1 that will count the number of days following the most recent date of the 4 cells above, in this example the result is "7". 2) Cell f1 that will show a date that is 3 days after the most recent date of the 4 cells, in this example the result is August 30/09. 3) Cell g1 that will count days based on two criteria: if all of the 4 cells (a1-d1) are dates in the past, then g1 will add 30 days to the most recent date and count the number of days from today to that date or if one of the dates in the 4 cells is a date in the future, then the value in g1 is simply the number of days from today to that date, in this example the result is "23". Thanks very much for your help with this. -- Brian |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formulas to count dates
Hi,
For the first one use =Z1-MAX(A1:D1) For the second one I note that your desired answer 8/30/09 doesn't follow from your stated question? If you want 3 days after TODAY then that would be 8/30/09 but 3 days after the dates you have given us would be 8/23/09? If you want the first of these =Z1+3 If you want the second of these =3+MAX(A1:D1) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Brian" wrote: I have input the following dates into cells A1,B1,C1,D1 respectively...March 30, 2009, August 20, 2009, July 30, 2008, November 20, 2008. Cell Z1 is todays date =TODAY() which is August 27. I would like three formulas as follows: 1) Cell e1 that will count the number of days following the most recent date of the 4 cells above, in this example the result is "7". 2) Cell f1 that will show a date that is 3 days after the most recent date of the 4 cells, in this example the result is August 30/09. 3) Cell g1 that will count days based on two criteria: if all of the 4 cells (a1-d1) are dates in the past, then g1 will add 30 days to the most recent date and count the number of days from today to that date or if one of the dates in the 4 cells is a date in the future, then the value in g1 is simply the number of days from today to that date, in this example the result is "23". Thanks very much for your help with this. -- Brian |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Dates between Dates exclude Text | Excel Discussion (Misc queries) | |||
Count Dates | Excel Discussion (Misc queries) | |||
I want to count the total Number of dates between two dates How? | Excel Worksheet Functions | |||
Count Dates? | Excel Worksheet Functions | |||
need to convert list of dates to count no. of dates by week | Excel Worksheet Functions |