Quick Excel Help: Date and Time Formats

I received an email from one of my readers asking a question about date and time formats in Excel and I figured I would share my answer here in case anyone else has the same question.

Question: Hi Nick, I am having trouble with Excel time date formats and was hoping you can help. I have a data field with a date time format and I want to separate this out with date in one column, time in another, & hour of the day  in another. Can you please tell me how to do this?

Answer: There are a number of date and time functions in Excel that should be helpful to you. I’m not sure if this is exactly what you’re looking for but try these formulas out:

In column a: =now()

Column b: =month(a2)&”/”&DAY(A2)&”/”&YEAR(A2)

Column c: =HOUR(A2)

Column d: =minute(A2)

Column e: =Second(A2)

Column F: =TIME(C2,D2,E2)

Do you have any other suggestions for this reader’s question?

How do you enter the current date in an Excel Spreadsheet?

One question I often get from Microsoft Excel users is if there is way to enter the current date into a cell and have it keep that date without changing if you open the same spreadsheet a few days from now. If you use the NOW function it will always display the current date.

The easiest way to enter the current date into a cell is to use the Ctrl-; key combination (press the Ctrl key and type a semicolon). Similarly, you can enter the current time by pressing Ctrl-Shift-; (press the Ctrl and Shift keys and type a semicolon). Both of these key combinations enter the information as a volatile value, not as a formula.