SOLVED

NOW() function

Copper 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 :) 

1 best response

Accepted Solutions
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

View solution in original post