• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Freezing a TODAY() date

glennpc

Member
I have a data table and it includes a STATUS column and a COMPLETED DATE column.


I have a formula in the COMPLETED DATE COLUMN that automatically plugs in the current date if the value in the STATUS column is "Completed". That formula is

[pre]
Code:
=IF([@Status]="Completed",TODAY(),"")
[/pre]

This works great but only for today. When I bring the sheet up tomorrow, all the COMPLETED DATES turn to tomorrows date! Is it possible to FREEZE them so they put in the value of today's date one time, and then don't subsequently change it (unless the user makes a mistake and changes the value in the status column back to something else (like "Started") and then later changes it to Completed? And can I do this WITHOUT VBA?
 
I guess VBA would work, but I have a lot of other VBA things happening on this particular worksheet. I would like to avoid an event-driven one if that's possible, but it probably is not. Any ideas?
 
Hi Glen ,


There was a lengthy discussion on a similar topic in the LinkedIn ExcelHero group , here :


http://www.linkedin.com/groups/Nonvolatile-nonVBA-date-function-3843467.S.196269867?trk=group_search_item_list-0-b-ttl&goback=%2Egmp_3843467%2Egna_3843467


Narayan
 
The additional overhead of changing a TODAY to a hard coded date should be pretty minimal - UNLESS you have a huge number of formulas 'downstream' of that TODAY formula, in which case Excel will recalculate all those formulas.


But that said, because TODAY is volatile, it's going to recalc those formulas each and every time you make a change in the workbook anyway. So the one-off hit of making the change is worth it.


See the demo file I've prepared on this at https://www.dropbox.com/s/n1j0r4waswj80u5/Volatility%20demo%20using%20TODAY%2020130628.xlsb


Can you elaborate on why the event-driven approach is likely to cause you an issue? Do you already have slow execution times for other event-driven things? If so, then we can help you fix that.
 
Jeff-- I'm not a super whiz-bang VBA coder (I get a LOT of help from you guys on Chandoo!), so I'm reluctant to use more event-driven things in this particular spreadsheet, since there are times when you have to shut it off then turn it back on or it interferes with other things and that gets a little confusing. One of my columns has some fairly complex event-driven code behind it that I got from Contextures (to allow multiple choices from a dropdown menu).


I'm trying to keep it simple at this point, so it is not too much a hardship for the user-- they just need to remember when they change a status to completed, to manually enter the current date in the Completed Date column. Easier to just eliminate the feature.


Thanks for the volatile-non-volatile write up. That will probably come in handy for other things in the future.
 
Hi Glen ,


In case you do decide to allow the user to enter today's date , you can try the DV drop-down tip given here :


http://www.mrexcel.com/forum/excel-questions/8483-non-volatile-timestamp-revisited.html


Narayan
 
Re: I'm reluctant to use more event-driven things in this particular spreadsheet, since there are times when you have to shut it off then turn it back on or it interferes with other things and that gets a little confusing.


I understand. But there's a whole bunch of experts reading this thread who can help. And it's really really simple for us to implement. Why not post a sample workbook, and let us have at it?
 
Back
Top