=TEXT(TODAY(),"YY") - How can I make the return a value?

[manc]

Good evening,

I'm using =TEXT(TODAY(),"YY") to return the current year in cell A1.
If I originally edited this workbook on December 31st 2017, saved the workbook, and only re-opened the workbook today, how can i stop cell A1 returning "18" and leave it as the original return value of "17"?

Best regards
manc

 

[sheetspread]

Can you tell when the cell was changed by looking at another on the sheet? If not you would need VBA or a manual edit to prevent date-dependent formulas from recalculating.

 

[HardBeatZ]

Your other option is to go into excel and turn on manual formulas. When you want to update the spreadsheet you can press F9 and it will run all your formulas including your date field.

 

[manc]

sheetspread, HardBeatZ,

Thanks for your replies.

I need it to record the first entry, as this is essentially our record of when the 'job' was raised.
VBA is definitely the answer - currently crawling the web for info...

Best regards
manc

 

[Jonmo1]

After entering the formula (back in 2017)
You could then copy that cell, and paste special - Values.

Or just type 17 instead of using a formula to begin with.

 

[manc]

Jonmo1,

You're right. Thinking about it logically, I can reference cell A1 from another cell where the user has typed the date and use =TEXT('sheet2'!A1,"YY")
I believe you might have been suggesting this also sheetspread?!

Thanks all for your prompt suggestions and ideas.

Greatly appreciated

Best regards
manc

 

[Jonmo1]

No, that's not what I was suggesting, but it works.

What I'm saying is wouldn't it be easier (physically fewer keystrokes) to just look at that cell on sheet 2, then just type 17 and press enter- three key strokes.

 

[sheetspread]

I can reference cell A1 from another cell where the user has typed the date and use =TEXT('sheet2'!A1,"YY")
I believe you might have been suggesting this also sheetspread?!

easier (physically fewer keystrokes) to just look at that cell on sheet 2, then just type 17 and press enter- three key strokes.

Both work well