Excel Formula Question

Question

Hello

Looking for some help to create a formula to populate a column in excel. In Column A I have a date and I need to populate column B with the factor Month. In column C that is highlighted would be the result I would need from the formula. The Factor Month (which is quarterly) is based on a completion date range and the populated. So for example the completion date for the project in row 2 is November 22, 2019 which falls between the range of September 1, 2020 and November 30, 2020 so column B should be populated with the Factor month of Oct-20.

Answer 1

I’ve just put up a file for you at:-

https://www.mediafire.com/file/a7oazfrrguim0ym/Excel_Formula_Question_1.xlsm/file

Download the file and open it clicking on:-

Enable Editing

 - if required.

File should open to Worksheet called:-

amended_orig_data

 - cell A 1.

I’ve added 4 new rows:-

J 2 to L 5 inclusive.

Answer 2

 

I’ve just put up a file for you at:-

 

https://www.mediafire.com/file/a7oazfrrguim0ym/Excel_Formula_Question_1.xlsm/file

 

Download the file and open it clicking on:-

 

Enable Editing

 

 - if required.

 

File should open to Worksheet called:-

 

amended_orig_data

 

 - cell A 1.

 

I’ve added 4 new rows:-

 

J 2 to L 5 inclusive.

 

I do have a follow up question if the data Start date, end date and display table is in a different work sheet than the complete date how would the formula be written to reference the different worksheet? Thanking you in advance.

Garry

Answer 3

Thankyou so much for your help. It was greatly appreciated. It was not working at the start but then I realized I did not have the correct format in Column B. :)

Answer 4

Or in B2

=LET(d, EOMONTH(A2, 0)+1, m, MOD(MONTH(d)-1, 3), EDATE(d, -m))

Format B2 as mmm-yy, then fill down.

Answer 5

See 2 pictures below.

Formula is in cell B 2 then copy down as required.

Some of your COMPLETE_DATES are outside of the Start Date and End Dates you've quoted.