Tom’s Tutorials For Excel: Separating Date and Time From NOW.
Here’s how you can store the date in one cell, and the time in another cell, of a source cell that contains both a date and time, such as if that source cell were holding the NOW
function.
This is different than just copying the NOW
function into 2 other cells and formatting those 2 other cells as date and time respectively. Formatting a cell only changes the appearance of its contents, but the underlying value in that scenario would still be both a date and time in all 3 cells.
Here, you are separating out the actual date value, and the actual time value, with this result:
Here are the steps by example.
Step 1
Start by entering the NOW
function as you see in cell A2.
Step 2
Maybe you’ll want your NOW
function to constantly update itself. But just for fun, if you want to make it constant (unchanging), select the cell, press F2
, then press F9
, then press Enter
.
Here is cell A2, selected with its constant date and time value seen in the formula bar:
Step 3
• Select the cell (B2 in this example) that will hold the date.
• In that cell, enter the formula =INT(A2)
.
• With that cell selected, right click it and select Format Cells.
Step 4
In the Format Cells dialog…
• Click onto the Number tab.
• In the Category pane, select Date.
• In the Type list, select your desired Date format.
• Click OK.
Step 5
• Select the cell (C2 in this example) that will hold the time.
• In that cell, enter the formula =MOD(A2,1)
.
• With that cell selected, right click it and select Format Cells.
Step 6
In the Format Cells dialog…
• Click onto the Number tab.
• In the Category pane, select Time.
• In the Type list, select your desired Time format.
• Click OK.
Leave a Reply