Subscribe
I have a column where I use (TODAY), so i can know when this sheet created, when I open this xl next day defently it will use system date and will replace the column. Is there a way once a user send me the xl sheet with a system date of his own column got locked?
Use of (NOW) or TODAY command
(13 posts) (5 voices)-
Thanks Faseeh, but I wanted to have an automated process. I have multiple user for this sheet and I as said earlier, though I need current date in one column automaticaly but once sheet saved by user that particular column locked its value. Giving liberty to users to cut and past is not possible for me.
Posted 4 months ago # -
Hi Tareen ,
What Jason meant was that you can use the keyboard shortcut CTRL ; ( press the keys CTRL and ; ( the semicolon ) together ) to insert today's date into a cell ; suppose your cursor is on J35 , and you press CTRL ; , the date 12/29/2012 will be entered in J35.
This is the same as manually typing in 12/29/2012.
When you repeat this action on December 30 , the date 12/30/2012 will be inserted.
Narayan
Posted 4 months ago # -
Hi Tareen ,
I doubt , but you can have a workaround ; if you can identify the cell(s) which should have today's date in it / them , then I suggest you lock this cell / these cells , so that the user cannot access it / them.
Then , you can also have a macro for the Close and Save operations ( Workbook_BeforeClose , Workbook_BeforeSave ) which can insert the system date into the cell(s) before the worksheet is closed / saved.
Protect the workbook so that users cannot access the macros / locked cell(s).
Is all this acceptable ?
Narayan
Posted 4 months ago # -
Thanks Narayn for the reply, I am sorry I am a new to XL world and didnt know how to use Macros or how to play with VB, let me discribe my wish.
I have a workbook consisting of 5 WS, I have a master WS where I have different dates, the most important is the ISO/Document Date, in this field I wanted to have current date with time (obvious choice is NOW for me), now problem is when a user send this workbook with current date, time of his system let say he saved workbook on 30th Dec 2012 and send it to me for processing, when i open this sheet on 31st Dec defentely date will get changed. Whereas I wanted to have same date which user provided to remain intact. As I said earlier I dont have knowledge of macros or vb so I was finding ways to use some formula for this. Much appreciated if you please guide or suggest me a script.
Regards,
Tareen
Posted 4 months ago # -
Hi Tareen ,
It's late at night here ; I'll reply tomorrow morning.
Narayan
Posted 4 months ago # -
Hi Tareen ,
I doubt that a formula based solution is possible unless all users are proficient enough in Excel to add a named range each time they save the workbook.
VBA would be the easiest way to do it.
In case you wish to read about a formula based solution , please go through this link ; I'll describe it in more detail tomorrow.
Narayan
Posted 4 months ago #
Reply
You must log in to post.
Welcome to Chandoo.org. New here?
Thank you so much for visiting us & taking first step to become awesome in Excel. Please use below links to navigate our site.
