Return dates in cells dependent on other cell saying weekly, fortnightly or monthly

[halesowenmum]

Hi.

I have a spreadsheet which I'm wanting people to submit figures for measurements they take and for them to know which dates they are due to provide a measurement.

Different people will be measuring at different frequencies - some weekly, some fortnightly and some monthly. I've already inserted a cell for them to fill in '1st measurement due date' so that they can set the day of the week or part of the month that they will be able to obtain the measure on eg it might be very Friday so they will pick a date for the Friday on which the first measurement is due (which gives us a reference point to work forward from). The cell for this date to be entered is AC25.

So in B26 there is a dropdown list for my three frequency values described above.

In cells I26 K26 M26 O26 Q26 S26 U26 W26 Y26 AA26 AC26 AE26 AG26 AI26 AK26 AM26 and AO26 I'd like it to populate those cells with the next date on which a measurement is due to be inputted. So if the user had selected Weekly from the dropdown, I26 would return a value of '1st measurement due date' + 1 week however, if they selected Monthly, it would return the appropriate date value for that, and the same for if they'd picked fortnightly.

How would I do that - formula? And if so, what. I don't want to be any more prescriptive than basic working days so just excluding weekends (folk can work out that if it's a bank holiday they need to measure earlier or defer or whatever).

Can anyone help?? I could probably cobble it together if it was just needing to return the value to 1 cell but I need it to go across in a line across columns to enable the filling in of the up to date information.

Yours hopefully!

 

[jmacleary]

Put this formula in I26: =IF($B$26="week",AC25+7,IF($B$26="fortnight",AC25+14,IF($B$26="month",DATE(YEAR(AC25),MONTH(AC25)+1,DAY(AC25)),"???")))
Put this formula in K26: =IF($B$26="week",I26+7,IF($B$26="fortnight",I26+14,IF($B$26="month",DATE(YEAR(I26),MONTH(I26)+1,DAY(I26)),"???")))

You can then copy and paste this formula to the other cells as the references will resolve themselves. I have assumed your dropdown values are week, fortnight and month. If an incorrect value is chosen the dates will all show as ???.

 

[halesowenmum]

Thank you jmacleary, that's really good.

The only bit that doesn't work quite right is the "Monthly" bit. If I pick say 28.12.18 as my first measurement date, choose Monthly from my drop-down, it populates across the cells but I guess because of the differing number of weeks in a month, it then goes out of whack. I'm presuming that if someone picked say the third Friday in the month they'd want the same kinda deal for each month, because that's presumably when they are able to get the measurement reading in by in each month. Hope that makes a modicum of sense!

So I'm thinking that if the person has picked say Fri 28 December as their first measurement due date, they'd thenn want 28 Jan, 28 Feb etc - but obviously with the variable numbers of weeks and thus Fridays in each month, it doesn't stay 'true' (iyswim).

What would you suggest??

 

[jmacleary]

Hi there, glad you like the formula. There are a few options. As written, the formula will produce the same day number for each month, like your example, so 28th of each month going forward, so this will obviously shift around, sometimes on a weekday, sometime on a weekend. If you want it to always hit say the 3rd friday of each month, then the formula would need to be more complex (I can work it out if you want). Your reply suggests to me that maybe the best thing would be to not have a monthly option, but go for 28 days, although this would mean that from time to time the same month would come up twice.

 

[halesowenmum]

Good morning!

Onto the topic of monthly formulas.

So, I've had a sleep and a good think about things and my logic is that people will pick a date from the month that best suits when the data they use for measuring is available. So it could be it's always ready by the 21st or it's always ready by the 28th - it's always on a weekday so if the 28th was a Saturday it would need to be Fri the 27th instead....

Would that be possible to do?????

 

[halesowenmum]

Could just the Monthly element of the formula be amended appropriately by using EDATE??? (Not sure how to write this into the existing formula, if it's even appropriate / workable).