Is it possible have Date and Time remain after file chasnged or saved

pedro-egoli

Well-known Member
Joined
Apr 25, 2004
Messages
1,217
Office Version
  1. 2016
Platform
  1. Windows
Interested to know if it is possible to use the Now Funtion in two columns , one for Date the other for time, and leave them unaltered when moving to cells before either saving and/or say reopening the next day.

This spreadsheet will show what happens now when I drag down from previous cells
Book1
ABCDEFG
1Datetime
224/03/200915:25thisshowedas24/3/200915:20whenIfirstdidit
324/03/200915:25ItchangedtothisfigurewhenIdraggedformuladown
Sheet1


Thanks again

Pedro
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Makrini

Well-known Member
Joined
May 22, 2007
Messages
1,035
now will operate every time your workbook calculates. You can turn off calculation but that kind of defeats the purpose.

If you are looking for a time stamp - A Macro is a better option. You can then link it to a single cell change event - or even a key.
 

pedro-egoli

Well-known Member
Joined
Apr 25, 2004
Messages
1,217
Office Version
  1. 2016
Platform
  1. Windows
Thanks Makrini, I thought that might be the answer , am just trying to solve a situation where the date and time of an event are recorded forever, by an easy drag down method.

No good with writing macros .
Great weather down here suppose the same in Brisbane

Pedro
 

Makrini

Well-known Member
Joined
May 22, 2007
Messages
1,035
Not sure what you mean? But sounds like using "=now()" then copy paste special values and drag THAT down?

And yeah great weather here too - but I don't have a beach to enjoy it on!

Got to work on getting work from home access - and laptop it in from sunny places sipping ****tails...
 

pedro-egoli

Well-known Member
Joined
Apr 25, 2004
Messages
1,217
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Makrini,
In spreadsheet I just wanted to be able to highlight say cells A3 and B3 and use the little black box on right hand bottom of B3 to drag formula down.

Sounds like you have you are heading in the right direction so far as workplace is concerned.

Pedro
 

Makrini

Well-known Member
Joined
May 22, 2007
Messages
1,035
You can use "=Now()" and drag it down...

then copy paste special values over the top of the formula
 

Ivan F Moala

MrExcel MVP
Joined
Feb 10, 2002
Messages
4,209

ADVERTISEMENT

There are other ways to do this BUT one way is ....
To use data Validation to keep your Dates / Time the same.
Just link the validation to a cell with the formula =Now()
and format your columns accordingly eg Date & Time format.
You then need to select the Date or time via the dropdown selector.
 

pedro-egoli

Well-known Member
Joined
Apr 25, 2004
Messages
1,217
Office Version
  1. 2016
Platform
  1. Windows
Thanks again Makrini, will try that later.

Thanks Ivan, your solution looks interesting except I am finding a problem with Validating Time

I selected Time /equal to/=Now() and custom formatted as " h:mm" .
When I dragged down it just dragged the previous time.
When I saved file it left Date the same , which it should today but I don't know how it will go tomorrow.
The Time remained the same as above.

Where am I going wrong?

Thanks
Pedro
 

pedro-egoli

Well-known Member
Joined
Apr 25, 2004
Messages
1,217
Office Version
  1. 2016
Platform
  1. Windows
Anybody able to help with this one please.

Pedro
 

Watch MrExcel Video

Forum statistics

Threads
1,122,822
Messages
5,598,307
Members
414,224
Latest member
Crazy_FC

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top