If statements??
 

I have a table of dates that looks like this:

FY2010.Q1 FY2010.Q2 FY2010.Q3 FY2010.Q4 FY2010TOTAL
2009.Sep.30 2009.Dec.22 2010.Mar.31 2010.Jun.30 2010.Jun.30
9/30/2009 12/22/2009 3/31/2010 6/30/2010 12/22/2009
92 83 0 0
175


I would like to create a formula that looks at the current date and gives me
the number of days in each quarter. When it comes to the quarter end, in
this case FY2010.Q1, then give me the number of days in the quarter. Answer
in above answer would be 92 days. For the current quarter I would need the
number of days for Oct, Nov, and Dec, assuming that today's date is
12/22/2009, therefore the answer would be 83 days and for the current fiscal
year the number of days would be 175.

This formula would also need to work if the data is expanded with dates that
look like this:

2009.Jul 2009.Aug 2009.Sep FY2010.Q1
2009.Jul.31 2009.Aug.31 2009.Sep.30 2009.Sep.30
7/31/2009 8/31/2009 9/30/2009 9/30/2009

With the same logic as the first scenario.

Thanks so much!

If statements??
 

This formula assumes you always have the previous period end date in the
previous column.

Assuming your "FY2010.Q1" is in A1, put this in b4, and copy across:
=max(min(today(),b3)-a3,0)

I'm not sure why you are putting Dec 22 in column B. Shouldn't it be Dec 31?

The number of days so far in the fiscal year is just the sum:
=sum(a4:d4)

Regards,
Fred

"LWilson" wrote in message
...
I have a table of dates that looks like this:

FY2010.Q1 FY2010.Q2 FY2010.Q3 FY2010.Q4 FY2010TOTAL
2009.Sep.30 2009.Dec.22 2010.Mar.31 2010.Jun.30 2010.Jun.30
9/30/2009 12/22/2009 3/31/2010 6/30/2010 12/22/2009
92 83 0 0
175


I would like to create a formula that looks at the current date and gives
me
the number of days in each quarter. When it comes to the quarter end, in
this case FY2010.Q1, then give me the number of days in the quarter.
Answer
in above answer would be 92 days. For the current quarter I would need
the
number of days for Oct, Nov, and Dec, assuming that today's date is
12/22/2009, therefore the answer would be 83 days and for the current
fiscal
year the number of days would be 175.

This formula would also need to work if the data is expanded with dates
that
look like this:

2009.Jul 2009.Aug 2009.Sep FY2010.Q1
2009.Jul.31 2009.Aug.31 2009.Sep.30 2009.Sep.30
7/31/2009 8/31/2009 9/30/2009 9/30/2009

With the same logic as the first scenario.

Thanks so much!

If statements??
 

Hi Fred,

Thank you for your response. In this report that I'm writing has a
calculation that uses the current number of days in the quarter, hence,
needing the current date in the FY2010.Q2 column. I'll try this formula and
see if it works. Thanks again!

"Fred Smith" wrote:

This formula assumes you always have the previous period end date in the
previous column.

Assuming your "FY2010.Q1" is in A1, put this in b4, and copy across:
=max(min(today(),b3)-a3,0)

I'm not sure why you are putting Dec 22 in column B. Shouldn't it be Dec 31?

The number of days so far in the fiscal year is just the sum:
=sum(a4:d4)

Regards,
Fred

"LWilson" wrote in message
...
I have a table of dates that looks like this:

FY2010.Q1 FY2010.Q2 FY2010.Q3 FY2010.Q4 FY2010TOTAL
2009.Sep.30 2009.Dec.22 2010.Mar.31 2010.Jun.30 2010.Jun.30
9/30/2009 12/22/2009 3/31/2010 6/30/2010 12/22/2009
92 83 0 0
175


I would like to create a formula that looks at the current date and gives
me
the number of days in each quarter. When it comes to the quarter end, in
this case FY2010.Q1, then give me the number of days in the quarter.
Answer
in above answer would be 92 days. For the current quarter I would need
the
number of days for Oct, Nov, and Dec, assuming that today's date is
12/22/2009, therefore the answer would be 83 days and for the current
fiscal
year the number of days would be 175.

This formula would also need to work if the data is expanded with dates
that
look like this:

2009.Jul 2009.Aug 2009.Sep FY2010.Q1
2009.Jul.31 2009.Aug.31 2009.Sep.30 2009.Sep.30
7/31/2009 8/31/2009 9/30/2009 9/30/2009

With the same logic as the first scenario.

Thanks so much!


.