Between dates question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ryan.fitzpatrick3@safeway.com - 05 Mar 2008 21:46 GMT

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

Reply to this Message

PCLIVE - 05 Mar 2008 21:57 GMT

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

=TEXT(TODAY(),"mmm")

HTH,
Paul

> I've seen a lot of posts about between dates questions. I have a excel
> sheet where I have =today()
[quoted text clipped - 10 lines]
>
> Ryan

Reply to this Message

Kevin B - 05 Mar 2008 22:01 GMT

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.

Signature

Kevin Backmann

> I've seen a lot of posts about between dates questions. I have a excel
> sheet where I have =today()
[quoted text clipped - 10 lines]
>
> Ryan

Reply to this Message

ryan.fitzpatrick3@safeway.com - 06 Mar 2008 17:06 GMT

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

> Try the following formula
>
[quoted text clipped - 21 lines]
>
> > Ryan

Reply to this Message

dan dungan - 06 Mar 2008 17:44 GMT

Hi Ryan

How about:

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

Dan

Reply to this Message

Roger Govier - 06 Mar 2008 17:44 GMT

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)

Signature

Regards
Roger Govier

> 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.
[quoted text clipped - 50 lines]
>>
>> > Ryan

Reply to this Message

PCLIVE - 06 Mar 2008 17:48 GMT

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

> 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.
[quoted text clipped - 50 lines]
>>
>> > Ryan

Reply to this Message

ryan.fitzpatrick3@safeway.com - 10 Mar 2008 23:34 GMT

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

> 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
[quoted text clipped - 82 lines]
>
> >> > Ryan

Reply to this Message