Tom’s Tutorials For Excel: Separating Date and Time From NOW.

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.