CODEWITHSUNDEEP
excel
zenarthra
zenarthra

Reputation: 17

How do i calculate the current date and time for single cell in excel?

How do i calculate the current date and time in excel? I have first column Name, second Date and third Time. The problem is when i change the name column, all previous entries of date and time change to the current one.

E.g

Name      Date      Time
George    7/6/2017  20:45
John      7/6/2017  20:45
Michael

If i change the next cell to Michael, all above entries change to the same date and same current time 

Name      Date      Time
George    7/6/2017  20:49
John      7/6/2017  20:49
Michael   7/6/2017  20:49

Here is the function for the calculation of the time:

=IF($J4<>0, NOW(),)

Here is the function for the calculation of the date:

=IF($J4<>0,TODAY(),)

How do i fix this, thanks?

Upvotes: 0

Views: 157

Answers (1)

Andy G
Andy G

Reputation: 19367

NOW() and TODAY() are functions. Whenever the worksheet recalculates - which happens, by default, whenever a value is entered into a cell, the results of these formulas will also change.

To insert the current date into a cell you can use the shortcut Ctrl-;, and Ctrl-Shift-; for the current time. These are fixed values and won't change after being entered.

It is possible to work with fixed values and formulas.

  • switch to manual calculation
  • fill in the rows, leaving the date/time formulas intact
  • copy/paste special, values, all the date/time formulas
  • switch back to automatic calculation

This is a risky strategy though. If you don't follow it exactly you could end-up with all the dates/times having the same value, with no way to reinstate the entered dates/times. It isn't worth the risk when the keyboard shortcuts are available.

Upvotes: 2

Related Questions