Date formula help - between static start and end date do this?

Jessica Miller

Hi,

Merry Xmas & happy new year...

I'd really appreciate some help with this. I need a formula that enters a number in a cell within column X when a between dates condition is met i.e.

If Start Date and End Date is between 30/12/20 - 30/01/21 populate cell with '1'

If Start Date and End Date is between 01/02/21 -01/03/21 populate cell with '2'

If Start Date and End Date is between ......... populate cell with '3'

Etc...

I can't use < Today formulas for this as I want a static reference point.

Should also add that right now I'm using the Month function.

This wouldnt work if the start/end date span Jan AND Feb (as i'd get 1 if i used start and 2 if i used end). Ideally i'd set my own result.

Any ideas? I'm struggling to find anything on the functions pages.

Thanks!

Paul Newcome

So if something is in Jan and Feb, would you want "1, 2" or would you want just "1" or just "2"? What if it spans more than 2 months? Would you want to record all of the months covered or just one of the particular months, or...?

Jessica Miller

Hi @Paul Newcome,

Thanks for coming back to me. I'm basically trying to achieve one result.

I think i've made this more complicated than it needs be, but basically for a 30 60 90 day plan that starts mid month, I'd like to be able to auto fill a column with one result: 30 or 60 or 90.

30 days spans half of jan and half of feb. 60 spans half of feb and march and so on.

Other than re-arranging my dates to start at beginning of calendar month (which is most sensible solution) I was just looking for an alternative option :)

Paul Newcome

I'm still not sure I follow. Are you able to provide a screenshot with some sample data entered that includes the values you would like to show based on the sample start and end dates?