Date and time Format Question

steve400243

Active Member
Joined
Sep 15, 2016
Messages
380
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,611
Office Version
  1. 365
Platform
  1. Windows
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.
 
Solution

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,598
Office Version
  1. 2013
Platform
  1. Windows
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

Active Member
Joined
Sep 15, 2016
Messages
380
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

MrExcel MVP
Joined
May 26, 2009
Messages
17,756
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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

Active Member
Joined
Sep 15, 2016
Messages
380
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Active Member
Joined
Sep 15, 2016
Messages
380
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP
Joined
May 26, 2009
Messages
17,756
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Active Member
Joined
Sep 15, 2016
Messages
380
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
14,611
Office Version
  1. 365
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,041
Messages
5,767,789
Members
425,435
Latest member
cmardaz

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