• 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.

Use of (NOW) or TODAY command

T

Tareen

New Member
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?
 
Hi Tareen,


You can simply copy paste that column with values only (not formulas) the date will not change you open the file later? Does that sound appealing??


Regards,
 
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.
 
BTW: another easy way to get the current date in a cell, but it will not update like that formula does is Ctrl+;
 
Dear Jason,


I tried but unable to get the result, will you please guide me step by step for use of

Ctrl+
 
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
 
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
 
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
 
Good day Tareen

I am sure NARAYANK991 will come up with a work around but can you just tell the users to type in the date and time it wouls only take a second or two.
 
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.


http://www.linkedin.com/groups/Nonvolatile-nonVBA-date-function-3843467.S.196269867?qid=9e97319e-bdd8-46d7-8383-43289dfd0046&trk=group_most_popular-0-b-ttl&goback=%2Egmp_3843467


Narayan
 
You must log in or register to reply here.
Back
Top