Adding different period units to a date.

[CPAC]

Hi, is there a formula for adding different periods to a date depending on what unit is selected from a drop down menu (days,weeks,months,years).

I can only find EDATE that works great but for months only... I've attached a file to show what I'm trying to achieve.

As always any help would be greatly appreciated

[AliGW]

Great! the only example you've given is ... Wait for it ... MONTHS!!!

Maybe try adding a few more lines of data that actually show the isuue you want help with??? Thanks.

[CPAC]

Hi, I'm really sorry if I've not given enough information. The formula just needs to calculate the new date if it was 6 days, 6 weeks, 6 months, or 6 years...

[MrShorty]

I don't think there is a single function that will do this, but it should be possible to piece together appropriate logic that will be able to do this. Here's the logic I see:

1) For months, the formula you have works just fine.
2) Years can be treated the same as months, but mutliply the value in F7 by 12 (there are 12 months in a year). EDATE(D7,F7*IF(H7="YEARS",12,1)) should be able to handle the months and years options.
3) Excels calendar system is based on days, so the option of days should be as simple as adding D7 and F7 D7+F7
4) There are 7 days in a week, so something similar to the months/years pairing should work for days D7+F7*IF(H7="WEEKS",7,1)
5) Now we just need some way to have Excel test for "DAYS/WEEKS" or "YEARS/MONTHS". =IF(OR(H7="DAYS",H7="WEEKS"),D7+...,EDATE(...))

There are certainly other ways to do it, but that should work.

[CPAC]

Thanks for the help Mr Shorty!

From your reply I've constructed the following formula for cell "J7". It would be great if someone could improve on this...

Please Login or Register  to view this content.

Thanks again.

[MrShorty]

Your formula looks fine. I notice that it is checking if H7="MONTHS" twice, and I don't see a reason for checking twice, but I don't expect it to create a problem.