have excel not calculate a certain cell

[jed38]

i have an excel sheet that has alot of calculations. i have one cell that calculates the pay period ending date based on todays date.
my question is, is it possible to have some sort of vba code so that that cell does not calculate automatically while the rest of the sheet does. so that when u open up an older file it wont automatically change the pay period ending date untill you manually update that cell. Is this even possible because i use the today function?

i tried making just that cell a data table and changing the calculation to automatic except for data table but that didnt work

any help would be awesome

thanks
JD

[AndyLitch]

There are two main methods to lock down a date/time based calculation

1. Copy the calculated cell and "paste special -> Values" into the same cell
2. Replace the volatile Now() or Today in the calculation with a fixed date/time.

[jed38]

im trying to keep the sheet automatic when i open the sheet that cell will calculate the next pay period ending date. what i want is to keep that cell from calculating automatically so when older files are open it will keep that previous calculated pay period ending date and not update it to the new one untill i want to recalculate that call.

i feel like the only way i could do this is with a VBA code but not really sure how to/if is possible

[AndyLitch]

Usually there comes a point in time where a specific record ends e.g. end of shift/account closure etc ... At that point you want to replace the volatile Now calculation with a time fixed time in/time out time start/time stop calculation.
Without seeing an example of your workbook it's impossible to know.

[jed38]

I attached the excel file
its basically a time sheet. on the first sheet "TMS" in cell V3 is the formula im talking about. it looks at todays date and will give when that pay period ending date is. I use this file every pay period and save one for each pay period. but the problem is when i go to look at a previous file (for a older pay period) it changes everything to this pay period dates. i would like to make it so that this cell only updates (calculates) when i want to (via text box or something) but i still want the rest of the worksheet to automatically calucalte.

I know it seems like alot of work for something that is easier to do just manually but if you know of a way that would be great

thanks

[AndyLitch]

05-18-13example.xls

The above sheet has a macro which locks and stops all calculations effectively freezing it at the current point in time. That would make it ready for archiving.

Please Login or Register  to view this content.

Running tthe scanworksheets macro will lock it down

[jed38]

that works but i ended up using a VBA code to calculate all the cells in the range i wanted, and changed the calculation to manual
so it updates everything in the range

i then made a text box and used the same code just changed the range to the cell i wanted to calculate when i click on that text box

below is the code

for the button range

Please Login or Register  to view this content.

for the automatic range

Please Login or Register  to view this content.

[AndyLitch]

Just be careful with relying on the manual calculation because other workbooks might reset Excel back to automatic calculation.

What you may need in a code module is :-

Please Login or Register  to view this content.

[jed38]

okay just put that in a new module
and this will prevent excel from updating my formula when i open it if its not already set to manual calculation?
if i open my timesheet from a file that already had it set to manual it should be fine right

thanks for the info i didnt think about that

[jed38]

i dont think my way will work i tried it out with the now() function and when i save it updates it the time, so i assume it will do the same with the today function