I will say 99% of my reports, I have to work with the time, I mean by that that there are at least minimum 2 cells, one with the date and one with the hour or both in 1 single cell. For instance:

When I am extracting a data from a source, generally, those cells are not formatted as a date but as general. By consequence, I am not working correctly with those cells, particularly true if I have to work with formula and I need those formulas to consider them as a date.

In this case, I need first, to format them correctly. If I click on the drop list of the “general” (the small arrow on the right side), I got a list of default options, and selecting “short date” or “long date” has no effect.

For excel to take in account as a date, I have 2 choices:

• Double click on the cell and validate it by clicking on the “check” or in another cell. It will change from “general” to “date” (F2) or “custom” (C2). The bad point is that I have to do it one by one.

• Or use the “text to columns” option, this is the quickest way because it will do it for the whole column.

First select the whole column then click on this option. You will get this popup.

Click on “next” until this popup.

Before to click on “finish”, select “date” and if you click on the drop list menu, you will see different options:

Select the one best for you, in this example, I will select DMY (Date Month Year).

Now if I select “long date”, I will see the change (for instance F3). If I want more option, I just have to right click on the cell and select “format cells”, here I can select the one best for me.

Now what happens if the cell contains the day and hour like the cell C2, if you do the same thing, it will change from “general” to “custom”. To see how it has been formatted, right click on the cell and select “format cells” to get this popup:

In case if I want to have the seconds (for instance C3), I just need to add “:ss” at the end, like this:

dd.mm.yyyy hh:mm:ss

For all cells in the column F, you can format the same way, go to the “format cells” option then select “custom” and in the “type” field, remove what you have then put:

dd.mm.yyyy hh:mm

As you can see in my example, in the column A, there is the word “CET”, in this case, no matter what you will do, excel will not take in account as a date meanwhile there is any kinds of word. To remove it, again I have 3 choices:

• Do it manually by removing it one by one, not really recommended if you have a lot
• Use the “replace” option, in this case, the original data will be changed
• Or use a formula so it will do it automatically, in this case, the original data will be kept.

For my example, I use the 3rd option and this is the formula I use for the cell C2:

=DATEVALUE(MID(A2,1,2)&"/"&MID(A2,4,2)&"/"&MID(A2,7,4))+TIMEVALUE(MID(A2,12,5))

And for D2:

=IFERROR(DATEVALUE(MID(B2,1,2)&"/"&MID(B2,4,2)&"/"&MID(B2,7,4))+TIMEVALUE(MID(B2,12,5)),"-")

NOTE: if I don’t format it as a date before, I will get a number like this:

For the “duration” column, as you can guess, I ask excel to calculate how many hours between the “end” and “start” time. In case if there is no end time like the cell B5, it will calculate based on the current day (for this example 17/09). This is the formula I use:

=IF(D2="-",NOW()-C2,D2-C2)

To show more than 24 hours, you have to format it so go to the “format cells” option and in the “type” field of “custom”, remove what you have and put:

[hh]:mm

The trick is to put “hh” in bracket because if you put without (hh:mm), it will show less than 24h hours.

This is the most common things to do when I work with the date and hour so with that, you will be able to work if you are not familiar with excel. For a macro version, read my topic Format the date using a macro in an excel report.