Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 683
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,934
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,346
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Count Dates between Dates exclude Text Ken Excel Discussion (Misc queries) 3 April 8th 09 07:59 PM
Count Dates Matthew Tyrus Excel Discussion (Misc queries) 2 October 16th 08 06:29 PM
I want to count the total Number of dates between two dates How? seshu Excel Worksheet Functions 3 February 7th 08 06:41 PM
Count Dates? msnews.microsoft.com[_3_] Excel Worksheet Functions 3 February 1st 08 06:22 PM
need to convert list of dates to count no. of dates by week neowok Excel Worksheet Functions 13 January 30th 06 04:54 PM


All times are GMT +1. The time now is 04:11 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"