Date and time Format Question

[steve400243]

Hello,
Wondering if anyone knows best way to get the current date and time in a cell with just entering the date. For example if I enter 9/3 in a cell I want the result to be 9/03/2021 10:34am. I have a custom Format set with m/d/yyyy hh:mm But get back just the date, the time comes back as 00:00. Thank you in advance.

 

[jasonb75]

It will only show what you enter. Excel is a powerful tool but not a mind reader, if you only enter a date then excel will assume that a date is what is needed. Likewise, if you only enter a time then it will assume that a date is not needed.

For current date and time, you could use Ctrl ; <space> Shift Ctrl ; <enter> or you could use a vba procedure to enter a timestamp when a specific action is performed.

 

[GWteB]

Dates are integer numbers, displayed in a special way. Times are decimal numbers, also displayed in a special way, defined by your format.
If you want a date as a result, you'll have to enter it manually, and that's what you did.
If you want to get a time as a result, you will have to enter it yourself, just like you did with the date.
Excel doesn't no in advance what your input will be.

 

[steve400243]

It will only show what you enter. Excel is a powerful tool but not a mind reader, if you only enter a date then excel will assume that a date is what is needed. Likewise, if you only enter a time then it will assume that a date is not needed.

For current date and time, you could use Ctrl ; <space> Shift Ctrl ; <enter> or you could use a vba procedure to enter a timestamp when a specific action is performed.

Kind of what I thought, Just wasn't sure. I have a VBA that I may use as you mentioned. Thanks for your reply.

 

[JoeMo]

If you start with a cell having a "General" format, you can get what you want by selecting the cell, pressing ctrl and colon/semicolon keys together, then space key, then shift + ctrl and colon/semicolon keys together.

 

[steve400243]

Dates are integer numbers, displayed in a special way. Times are decimal numbers, also displayed in a special way, defined by your format.
If you want a date as a result, you'll have to enter it manually, and that's what you did.
If you want to get a time as a result, you will have to enter it yourself, just like you did with the date.
Excel doesn't no in advance what your input will be.

Thank you for the Explanation, Thought I was missing some magic formula.

 

[steve400243]

If you start with a cell having a "General" format, you can get what you want by selecting the cell, pressing ctrl and colon/semicolon keys together, then space key, then shift + ctrl and colon/semicolon keys together.

Thank you for the Reply, Appreciate it. JasonB Mentioned this in the message above - Ctrl ; <space> Shift Ctrl ; <enter>

 

[JoeMo]

Thank you for the Reply, Appreciate it. JasonB Mentioned this in the message above - Ctrl ; <space> Shift Ctrl ; <enter>

I saw Jason's reply, but I think it's missing the inclusion of holding down the colon/semicolon key when the ctrl key is pressed.

 

[steve400243]

I saw Jason's reply, but I think it's missing the inclusion of holding down the colon/semicolon key when the ctrl key is pressed.

In testing I got it working as needed by Holding down the Keys as you noted. Thanks for pointing that out! Have a nice Weekend!

 

[jasonb75]

I think it's missing the inclusion of holding down the colon/semicolon key when the ctrl key is pressed

I used the characters instead of naming them, if the semicolons in the post were for grammatical purposes then there wouldn't be spaces in front of them.