I will explain the different ways I use the time and date that I use for my different reports. I will start first by showing the month number of a month name. The formula in B2:

=MONTH(DATEVALUE(A2&"1"))

I will add a column and I will put some days. The formula in D2 to get the full date including the current year:

=C2&"/"&B2&"/"&YEAR(TODAY())

I will add a column and I will put some date. The formula in F2 is telling me if the specific date in E2 is listed in the column D:

=IF(COUNTIF($D$2:$D$4,E2),"yes","No")

I have a date and I need to know the first day and the last day of the month:

first day in B2: =EOMONTH(A2,-1)+1

last day in C2: =EOMONTH(A2,0)

For the last day, I have to format the cells in "short date".

Now I want to know which day of the week for every last day, in the cell D2:

=TEXT(C2,"dddd")

I want to extract the date and the hour:

in the cell B2: =INT(A2)

in the cell C2: =MOD(A2,1))

For the column B, I have to format to "short date" and for the column C, in "time".

I have a start and end time, and I want to know if the time in C2 is between them:

=$C$2=MEDIAN(A2,B2,$C$2)

=$C$2=MEDIAN(A2:B2,$C$2)

NOTE: both will give the same result. The difference is that for the second one, your cells need to be right beside each other.

Now I would like to know the duration, here I will show you 2 formulas:

In cell C2: =(B2-A2+(B2<A2))*24

In cell D2: =B2-A2+(B2<A2)*24

As you can see the difference of the formulas is minimum but the results are totally differents including its utilization. If I change the format from “time” to “general”, the numbers are different. To resume, the formula of the column C is showing the correct result in number and the column D in time. Based on my report, one will work better than the other.

I have a time and I want to add some numbers, the formula is just a simple “=A2+B2” but as you can see, the results are not correct so the correct formula is:

=A2+B2/24

I have a date column and I need to display the date with a time. The formula in B2:

=A2+14/24

This formula will need 2 extra works, first, in the cell below B3, I will have to change 14 by 22 and in cell B4, by 9. Secondly, I will need to format the cell by customizing it with “dd/mm/yyyy hh:mm”. Of course, it will be easier if I have a time column because the formula will be “=A2+B2”. Now I will add 10 hours so the formula in C2:

=B2+10/24

I want to convert my time to the time of a different country, my formula in D2:

=$C$2+B2/24

As we can see, D4 is not showing correctly. This is because I didn’t specify a date in the cell C2 and excel put automatically the date 00/01/1900 including the converted hours so I will put a specific date and now all are corrects.

Those exercises are just to show you that the 24h time should be always divided by 24 in order to get the correct value combining with other functions and that in some situations, it is important to not forget to use a specific date.