SOLVED

NOW() function

%3CLINGO-SUB%20id%3D%22lingo-sub-2388499%22%20slang%3D%22en-US%22%3ENOW()%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2388499%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20everyone%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20the%20past%206%20hours%20I've%20been%20trying%20to%20figure%20out%20how%20to%20get%20an%20automatic%20timestamp%20in%20Excel.%20I%20would%20like%20to%20know%20if%20it%20is%20possible%20to%20use%20the%20NOW()%20in%20an%20IF().%20Form%20example%3A%20if%20%22X%22%20is%20put%20in%20B3%20i%20would%20like%20the%20time%20at%20that%20exact%20time%20put%20into%20B7%20and%20the%20time%20won't%20change%20every%20time%20the%20formula%20is%20used.%20Even%20if%20that%20would%20mean%20i%20would%20have%20to%20at%20a%20a%20new%20sheet%20and%20put%20in%20all%20the%20hours%20and%20minutes%20in%20by%20hand.%20I%20know%20there%20must%20be%20a%20way%20to%20make%20it%20work%2C%20but%20i%20can't%20seem%20to%20figure%20it%20out.%20I've%20been%20using%26nbsp%3B%3DIF(%24B4%3D%22X%22%3BNOW()%3B%22Free%22)%2C%20the%20%22X%22%20is%20just%20an%20example%20for%20what%20needs%20to%20be%20put%20in%20for%20it%20to%20make%20a%20timestamp.%20It%20would%20be%20nice%20if%20it%20can%20stay%20just%201%20symbol.%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20so%20much%20in%20advance!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2388499%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMicrosoft%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2388574%22%20slang%3D%22en-US%22%3ERe%3A%20NOW()%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2388574%22%20slang%3D%22en-US%22%3Etimestamps%20are%20a%20common%20question%2Frequest.%20There%20are%20tricks%20using%20circular%20reference%20to%20create%20a%20persistent%20timestamp%20(i.e.%20it%20doesn't%20change)%20but%20they%20will%20also%20require%20changing%20the%20sheet%20setting%20to%20allow%20circular%20refs%20but%20limit%20the%20re-iterations.%3CBR%20%2F%3Eyou%20can%20also%20use%20a%20macro%20to%20create%20a%20timestamp%20base%20on%20a%20certain%20event%3CBR%20%2F%3Ebut%20the%20easiest%20thing%20to%20do%20if%20you%20just%20want%20a%20shortcut%20to%20enter%20the%20date%20and%2For%20time%20is%20to%20use%20the%20shortcuts%3A%20ctrl-%3B%20for%20date%20and%20ctrl-shift-%3B%20(or%20ctrl-%3A)%20for%20the%20time%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2389174%22%20slang%3D%22en-US%22%3ERe%3A%20NOW()%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2389174%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3BThank%20you%20so%20much%20for%20your%20reply%20even%20though%20this%20is%20a%20frequently%20asked%20question!%20I%20think%20you%20gave%20me%20just%20the%20right%20info%20to%20search%20up%20some%20ways%20to%20make%20this%20work%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello everyone, 

 

For the past 6 hours I've been trying to figure out how to get an automatic timestamp in Excel. I would like to know if it is possible to use the NOW() in an IF(). Form example: if "X" is put in B3 i would like the time at that exact time put into B7 and the time won't change every time the formula is used. Even if that would mean i would have to at a a new sheet and put in all the hours and minutes in by hand. I know there must be a way to make it work, but i can't seem to figure it out. I've been using =IF($B4="X";NOW();"Free"), the "X" is just an example for what needs to be put in for it to make a timestamp. It would be nice if it can stay just 1 symbol.   

 

Thank you so much in advance!

2 Replies
best response confirmed by allyreckerman (Microsoft)
Solution
timestamps are a common question/request. There are tricks using circular reference to create a persistent timestamp (i.e. it doesn't change) but they will also require changing the sheet setting to allow circular refs but limit the re-iterations.
you can also use a macro to create a timestamp base on a certain event
but the easiest thing to do if you just want a shortcut to enter the date and/or time is to use the shortcuts: ctrl-; for date and ctrl-shift-; (or ctrl-:) for the time

@mtarler Thank you so much for your reply even though this is a frequently asked question! I think you gave me just the right info to search up some ways to make this work