Date calculation using a range of dates

[govitz]

I know I should be able to do this with Excel, but can never get this to work. It involves 2 worksheets within a workbook. The first sheet contains dates and I want it to return the fiscal month for the date. The second sheet contains the table for the fiscal months. The second sheet is to use the comparison of greater than the first date and less than the second date. Please see my examples below.

WORKSHEET 1 - DATES
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]DATE[/TD]
[TD]MONTH[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]03/23/2016[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]01/03/2016[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]04/05/2016[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]03/31/2016[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

WORKSHEET 2 - FISCAL MONTHS
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]MONTH[/TD]
[TD]BEGIN[/TD]
[TD]END[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]12/31/2015[/TD]
[TD]01/31/2016[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD]01/30/2016[/TD]
[TD]02/28/2016[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]3[/TD]
[TD]02/27/2016[/TD]
[TD]03/27/2016[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]4[/TD]
[TD]03/26/2016[/TD]
[TD]05/01/2016[/TD]
[/TR]
</tbody>[/TABLE]

I would expect the results column B (Month) to return in Worksheet 1 - Dates to be:
03/23/2016 = 3
01/03/2016 = 1
04/05/2016 = 4
03/31/2016 = 4

How can I accomplish this in Excel? I am using Excel 2003 (yes, I know it's old).

Thanks,
Govitz

 

[j_unsuitable]

Hey,

have you tried =Month(A2) ?

nevermind, just saw the second tab

Can you move the "Month" in your second Sheet into column D?
If so a vlookup should workd
=Vlookup(A2, Sheet2!B:D,3,TRUE)

Julian

 

[j_unsuitable]

And now I saw some of the dates are overlapping
Like month three ends on March 27th, but month four begins on March 26th, so to which month does March 27th belong?

 

[govitz]

And now I saw some of the dates are overlapping
Like month three ends on March 27th, but month four begins on March 26th, so to which month does March 27th belong?

The dates were that way because I thought it would be calculated off a greater than / less than equation. If that is not the case they can be changed.

Yes the month can be moved to column D. Whatever it takes to make this work, I can adapt to it.

 

[j_unsuitable]

If you use the vlookup you actually only need the Begin Date and the month you want to start on that date and then use the vlookup on it. Its just important that you use TRUE and not FALSE, so

=Vlookup(A2, Sheet2!A:B,2,TRUE)

should do the trick, where Sheet2!A holds the Begin date and Sheet2!B the Month number

 

[jeffreybrown]

How about in B2 copied down -->> =LOOKUP(A2,Sheet2!$B$2:$B$5,Sheet2!$A$2:$A$5)

Data Range
[Table="class: grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][/tr]
[tr][td]

1​

[/td][td]

DATE​

[/td][td]

MONTH​

[/td][/tr]

[tr][td]

2​

[/td][td]

3/23/2016​

[/td][td]

3​

[/td][/tr]

[tr][td]

3​

[/td][td]

1/3/2016​

[/td][td]

1​

[/td][/tr]

[tr][td]

4​

[/td][td]

4/5/2016​

[/td][td]

4​

[/td][/tr]

[tr][td]

5​

[/td][td]

3/31/2016​

[/td][td]

4​

[/td][/tr]
[/table]

 

[govitz]

How about in B2 copied down -->> =LOOKUP(A2,Sheet2!$B$2:$B$5,Sheet2!$A$2:$A$5)

Data Range
[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

DATE​

[/TD]
[TD]

MONTH​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

3/23/2016​

[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

1/3/2016​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

4/5/2016​

[/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

3/31/2016​

[/TD]
[TD]

4​

[/TD]
[/TR]
</tbody>[/TABLE]

Not quite what I was looking for. I want to see if the date in sheet1 column A is within the range of sheet 2 columns b&c, if true, return sheet2 column A whichever row the range falls in. I'm thinking I have to have an array to test the range of greater than / less than dates, but I'm not sure.

 

[jeffreybrown]

Not quite what I was looking for.

Did you test it and how did it return something different than what you desire?

Maybe some samples?

 

[govitz]

Did you test it and how did it return something different than what you desire?

Maybe some samples?

Here is sample of Worksheet 1 (Sheet is named DATA) where I want to test DATE SENT and return the month value in MONTH. The "510502" is what was returned with the following calculation: =SUM(H2,YR!C3:C14,YR!B3:B14). YR is the worksheet that has the fiscal calendar

WORKSHEET DATA:
[TABLE="width: 1324"]
<tbody>[TR]
[TD]FIRM/
BUDGET[/TD]
[TD]RFQ #[/TD]
[TD]CUST #[/TD]
[TD]CUST NAME[/TD]
[TD]DESCRIPTION[/TD]
[TD]DATE REC[/TD]
[TD]DATE REQ[/TD]
[TD]DATE SENT[/TD]
[TD]SALESREP[/TD]
[TD] SELL $ [/TD]
[TD]PROD LINE[/TD]
[TD][/TD]
[TD]MONTH[/TD]
[TD]BROWN / LYLE[/TD]
[TD]FIRM / BUDGET[/TD]
[TD]NEW / REQUOTE[/TD]
[/TR]
[TR]
[TD]Firm[/TD]
[TD]15-18215-01R[/TD]
[TD]1430000[/TD]
[TD]ADO PRODUCTS[/TD]
[TD]Controls Upgrade 13340[/TD]
[TD]2/18/2016[/TD]
[TD][/TD]
[TD]2/18/2016[/TD]
[TD]Daniel Bartholomew[/TD]
[TD] $ 13,750.00 [/TD]
[TD]Kits[/TD]
[TD][/TD]
[TD]510502[/TD]
[TD]B[/TD]
[TD]F[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]Firm[/TD]
[TD]16-18532[/TD]
[TD]77016[/TD]
[TD]Advanced Tooling Specialists[/TD]
[TD]152PV Special[/TD]
[TD]3/17/2016[/TD]
[TD][/TD]
[TD]3/17/2016[/TD]
[TD]Brian Crawford[/TD]
[TD] $ 25,210.00 [/TD]
[TD]Continuous[/TD]
[TD][/TD]
[TD][/TD]
[TD]L[/TD]
[TD]F[/TD]
[TD]#VALUE![/TD]
[/TR]
[TR]
[TD][/TD]
[TD]16-18506[/TD]
[TD]1382000[/TD]
[TD]American Wick Drain Corporation[/TD]
[TD]Prootype Tooling Components[/TD]
[TD]3/9/2016[/TD]
[TD]3/9/2016[/TD]
[TD]3/9/2016[/TD]
[TD]Robert Gordert[/TD]
[TD] $ 53,300.00 [/TD]
[TD]Tooling[/TD]
[TD][/TD]
[TD][/TD]
[TD]B[/TD]
[TD]F[/TD]
[TD]#VALUE![/TD]
[/TR]
</tbody>[/TABLE]

WORKSHEET YR: (The data begins in column A and ends in column D)
[TABLE="width: 271"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Year[/TD]
[TD]Month[/TD]
[TD]Begin[/TD]
[TD]End[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD]1[/TD]
[TD]12/31/2015 [/TD]
[TD]1/31/2016[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD]2[/TD]
[TD]1/30/2016[/TD]
[TD]2/28/2016[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD]3[/TD]
[TD]2/27/2016[/TD]
[TD]4/3/2016[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD]4[/TD]
[TD]4/2/2016[/TD]
[TD]5/1/2016[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD]5[/TD]
[TD]4/30/2016[/TD]
[TD]5/29/2016[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD]6[/TD]
[TD]5/28/2016[/TD]
[TD]7/3/2016[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD]7[/TD]
[TD]7/2/2016[/TD]
[TD]7/31/2016[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD]8[/TD]
[TD]7/30/2016[/TD]
[TD]7/31/2016[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD]9[/TD]
[TD]8/27/2016[/TD]
[TD]10/2/2016[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD]10[/TD]
[TD]10/1/2016[/TD]
[TD]10/30/2016[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD]11[/TD]
[TD]10/29/2016[/TD]
[TD]11/27/2016[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD]12[/TD]
[TD]11/26/2016[/TD]
[TD]1/1/2017[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Thank you for all your assistance on this solution. It is truly appreciated!

 

[jeffreybrown]

The "510502" is what was returned with the following calculation: =SUM(H2,YR!C3:C14,YR!B3:B14)

Not entirely sure where that formula came from as it doesn't appear to ever have been suggested.

I M2 on the Data sheet, try -->> =LOOKUP(H2,YR!$C$2:$C$13,YR!$B$2:$B$13)