am using sheets and I want my cell to auto-populate with the date today in one cell and the time now to populate in another, so this is what I've tried doing.

I added the forumla in column C to show the time now, using the NOW() function, but it returns both date and time, when I only want the time, and everytime I make a new entry in Column B or anywhere in the sheet the entire Column C updates as show in the picture having the same seconds

In column A I added this formula =IF(ISTEXT(B1), TODAY(), "") it show the exactly what I want but I don't know if it will update the entire column once I enter a new data in column B the next day.

What I got got from trying

The result I want my sheet to do is this one.

What I want my sheet to do

It's a real life example on how this sheet will be used, it's like a logging entry for employees, like a tracker,whenever they put their name on Column B, Column A and C, should auto populate with the Date now, and the current time they entered their name, and it should not update when someone else updates the sheet with their own entry.

CodePudding user response：

if you are ok to turn on Iterative Calculations, then you can try this...

1. Open the google sheets file.
2. Click File in the top menu bar
3. Go to Settings
4. Change to the Calculation tab
5. Set Max number of iterations to 1
6. Click Save settings
7. Put this formula into cell C1

=ARRAYFORMULA(IF(ISBLANK($B:$B),"",IF(C:C="",NOW(),C:C)))

8. Select the entire column C
9. Click Format
10. Hover over Number
11. Select Time

NOTE: You can change the time format to whatever custom format you like.

To do the same for the date, just change the formula to:

=ARRAYFORMULA(IF(ISBLANK($B:$B),"",IF(A:A="",NOW(),A:A)))

and put it into cell A1 Then follow steps 8-11 as above, but selecting column A and using a Date format

NOTE: If you get a date for the year 1899 in cell A1, then copy the data from cell B1, then delete the contents of cell B1, then paste it back in again, and it will return the current date/time.