Using a variable in a formula

[ingasos]

I've seen a similar question to the append with the heading of "Using a variable in a formula", but I can't fine the correct syntax for my particular problem which is as follows:

=DATE(YEAR(summary_start_date)+1,MONTH(summary_start_date),DAY(summary_start_date))


I want to change the + part where it says +1 to a variable called year_asc_dec which could either be a + or a - so I would end up with +1 or -1 in the above formula.

Any thoughts or suggestions. Basically a want to produce a date table which is either descending or ascending from a given start date.


Thanks in advance for any thoughts
Siggi

 

[FormR]

I want to change the + part where it says +1 to a variable called year_asc_dec which could either be a + or a - so I would end up with +1 or -1 in the above formula.

Hi, you could try like this:

=DATE(YEAR(summary_start_date)+(year_asc_dec&1),MONTH(summary_start_date),DAY(summary_start_date))

 

[Fluff]

Hi & welcome to MrExcel.
How about
=DATE(YEAR(summary_start_date)+year_asc_dec,MONTH(summary_start_date),DAY(summary_start_date))

 

[Joe4]

Welcome to the Board!

Variables are found in VBA. Excel formulas may used "named ranges", but not really variables.
So, where are you talking about doing this, in VBA or in an Excel formula on the sheet?

If "year_asc_dec" is a named range of a single cell holding a number, then you just update your formula to work like the other parts of it, i.e.

[COLOR=#1D2129][FONT=Helvetica]=DATE(YEAR(summary_start_date)[/FONT][/COLOR][B]+[COLOR=#ff0000]year_asc_dec[/COLOR],[/B][B]MONTH(summary_start_date),DAY(summary_start_date))[/B]

 

[ingasos]

Hi, you could try like this:

=DATE(YEAR(summary_start_date)+(year_asc_dec&1),MONTH(summary_start_date),DAY(summary_start_date))

WOW - that works!!!!! I had been close to the solution, but I missed out the + at the start of this section +(year_asc_dec&1)

Many Many thanks

Siggi

 

[ingasos]

Thanks Joe4 unfortunately your suggestion did not work for me, but as you can see someone came back with the working solution

Thanks again for looking at my problem
Siggi

 

[ingasos]

Hi & welcome to MrExcel.
How about
=DATE(YEAR(summary_start_date)+year_asc_dec,MONTH(summary_start_date),DAY(summary_start_date))

Thanks Fluff for having a look at my problem / challenge!!Fluff. Unfortunately it did not work but again it was close. This one worked a treat

=DATE(YEAR(summary_start_date)+(year_asc_dec&1),MONTH(summary_start_date),DAY(summary_start_date))

 

[Fluff]

Glad you got a solution & thanks for the feedback

 

[ingasos]

Thanks everyone who helped out and coming back with suggestions. It's great to see that this discussion group people people out like myself.

Hopefully I can help someone out in the future.

Siggi

 

[Joe4]

Sorry, I misunderstood/misread the question!
Glad you got an answer that works.