Between dates question

[ryan.fitzpatrick3]

I've seen a lot of posts about between dates questions. I have a excel
sheet where I have =today()
I have another cell lets say b10 where I would like if today is
between 1/1/2008 and 1/31/2008 it'll say Jan, if between 2/1/8 and
2/29/08 is Feb and so on. I've seen this code

=IF(AND(M7>=DATEVALUE("8/1/08"),M7<=DATEVALUE("10/24/08")),M6,3)

on the posts but i'm unsure if it's what I want, also do I need to do
a case selection for each month so if today is in a certain month
it'll bring back that month respectively. I thought this might be to
big for a nested if function. Any thoughts? thanks in advance.

Ryan

 

[PCLIVE]

I'm not sure what you're trying to achieve here, but one way:

=TEXT(TODAY(),"mmm")

HTH,
Paul

 

[Kevin B]

Try the following formula

=TEXT(B10,"MMM")

The formula extracts the month from the date in B10, converts it to text and
formats the result for a 3 character abbreviated month.

 

[ryan.fitzpatrick3]

Thank you for the reply but that's not what I am looking for. I'll try
to explain to the best of my ability.

cell A1 has =today()

in b10 I have the period I need. we have 13 financial periods for the
company i work for.

What I would like is in cell b10 to know the date in a1 (=today())
falls between the period dates. I was thinking about a case statement.

case Period 1
if date is between 1/1/08 and 1/26/08 then equals "1"
case Period 2
if date is between 1/27/08 and 2/23/08 then equals "2"
case Period 3
if date is between 2/24/08 and 3/22/08 then equals "3"
case Period 4
if date is between 3/23/08 and 4/19/08 then equals "4"
case Period 5
if date is between 4/20/08 and 5/17/08 then equals "5"
etc etc to period 13

so if today() falls between the case statement that has the set dates
it'll give the answer 1, 2, 3, etc. Does this make since?

Ryan

 

[dan dungan]

Hi Ryan

How about:

Cell A1 =today()
Cell B10==TEXT(A1,"mmm")

Dan

 

[Roger Govier]

Hi Ryan

Enter in column A the dates and column B the period number as follows
0 1
1/26/08 2
2/23/08 3
3/22/08 4
etc

Then
=VLOOKUP(TODAY(),$A$1:$B$13,2,1)

 

[PCLIVE]

I'm not sure how your periods are determined. However, I would setup a
table somewhere on your worksheet. Column 1 of your table would be the
starting date of each period and column 2 would be the ending date of each
period. Column 3 would be the period number. Then you could use a formula
like this:
=SUMPRODUCT(--(TODAY()>=M1:M13),--(TODAY()<=N1:N13),O1:O13)

Note: M1:M13 represents column 1, N1:N13 column 2, O1:O13 column 3.

1/1/2008 1/26/2008 1
1/27/2008 2/23/2008 2
2/24/2008 3/22/2008 3
3/23/2008 4/19/2008 4
4/20/2008 5/17/2008 5
5/18/2008 6/14/2008 6
6/15/2008 7/12/2008 7
7/13/2008 8/9/2008 8
8/10/2008 9/6/2008 9
9/7/2008 10/4/2008 10
10/5/2008 11/1/2008 11
11/2/2008 11/29/2008 12
11/30/2008 12/27/2008 13




HTH,
Paul

--

 

[ryan.fitzpatrick3]

Thank you the sumproduct was perfect! Sumproduct is a great tool.
Awesome, thank you!