Thank you the sumproduct was perfect! Sumproduct is a great tool.
Awesome, thank you!
On Mar 6, 10:48 am, "PCLIVE" <pclive(RemoveThis)@cox.net> wrote:
> 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.fitzpatri...@safeway.com> wrote in message
>
> news:7134e1eb-6c53-442f-bd11-(E-Mail Removed)...
>
> > 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
>
> > On Mar 5, 2:01 pm, Kevin B <kbackm...@sbcglobal.net.spamBgone> wrote:
> >> 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.
>
> >> --
> >> Kevin Backmann
>
> >> "ryan.fitzpatri...@safeway.com" wrote:
> >> > 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
Similar Threads
