How to create a functional drop down menu to update expiry dates.

[Robert2013]

Hello

I am a complete novice when it comes to excel and have got so far in my small project through internet tutorials but am now stuck.

I am creating a very simple spreadsheet to manage my gym memberships. It basically has membership number, first name, last name, membership type (drop down box) start date and expiry date. I have put in conditional formatting so that the expiry date goes red when expired but i want to try and automate the inputting of the dates so for example.

If i select '1 week membership) from the drop down box in the membership type box it will firstly change the start date to the current date (i think this is using the NOW() function) and secodnly changes the expriry date to todays date plus 6 days. Ofcourse i want the expiry date to increase depending on the selection so if i select 1 month membership it would be now date plus 28 days.

I am stuck as to how i can do this and from internet tutorials have been told it requires macro as it cant be done any other way?

Please help if possible.

[Jakobshavn]

Please up-load a copy of your current workbook. There are insufficient details in the post for us to offer up a complete solution.

[protonLeah]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.


Attach a sample workbook.

When you are in Advanced reply, click on the paperclip to open the upload window.

View Pic

[Robert2013]

I have attached the file so hopefully you can see what im trying to achieve. Thanks

[Fotis1991]

See the example sheet. You just make a choice from your list in column D and Excel calulate the TODAY date in Column E and the expired date in next column.

Pls. pay attention that formula in Column E uses CIRCULAR REFERENCE. rEAD HERE ABOUT THIS.

http://chandoo.org/wp/2009/01/08/tim...-formula-help/

[Robert2013]

is there certain figures i should set the interations to?

[Robert2013]

sorry when i select 1 week the expiry date is 1 month ahead?

[Robert2013]

also a month to us is 28 days how can i change it to this?

[Robert2013]

Fotis Thank you for this, not sure if im midding something but when i select an option the expiry date is simply todays day ?

[Robert2013]

Sorry I am even confusing myself now - Basically your formula is great however I want to alter the amount of days increased per selection so for 1 week i want it to be the start date plus 6 and a month is counted as 28 days in the gym so i need to alter this and also when i select year it does not change the expiry date by 1 year instead just by 1 month. Could you please explain to me how to make these changes to the formula, is this made using macros or just formula?

[Fotis1991]

1) Maximum Iterations must be 1

2) You are correct when you say that if you choose 12 months, my formula didn't work(my mistake), but now as you need to count every month as 28 days, there is another formula to use. This one

=IF(D4="","",IF(COUNTIF(D4,"*Week*")>0,E4+6,E4+INDEX($J$4:$J$8,MATCH(D4,$I$4:$I$8,0))*28))

This add 6 days if the choice is "1 week" and add months depanting of your choice,calulating eatch month x 28 days.

See the example.

[Robert2013]

Fotis

Thank you for this you are a life saver, would you be ok with me using the example work book you have attached rather than copying the formulas over into my own?

Also how would I change the font size etc in the expiry date columb in excel 2010?

[Robert2013]

Figured out the font issue.

[Fotis1991]

My suggestion is(as i believe that it's better to learn to use the formulas in your files) to try step by step to copy and modify-if needs-my formulas to your workbook.

[Robert2013]

OK thanks you, i will see how i get on, when i tried copying the formula into my other work boo i kept getting a date like 1-1-1899 ?

[Fotis1991]

In an empty cell, type =today()

What to you get?

[Robert2013]

i get 1/15/2013 which is correct
??

[Fotis1991]

In which column do you get the "1-1-1899 " date? In column with date stamp? Did you fix your iterations for avoid circular reference?

If you can not handle this, remove the Names and anything else sensitive data and upload this sheet to check it.

[Robert2013]

I think I have fixed the issue, i had to clear the 'membership type' selection and re select it. Thanks