The date and time are important values to take in account for some of my reports but extracting this information, it happen that the cell contains also some words like CET, EST, etc. to indicate the time zone code so I need to remove it. The alternative option is to use the “replace” tool.

 

When I use the formula ?

When I need to display the date and time only to calculate for instance the duration of the incident. The fact to not use the “replace” tool but the formula, it is automatic and faster.

 

How to use the formula ?

The formula in this topic is with "," so depending of the operating system of your PC, the formula should have ";" instead of ",".

 

How is/are the formula(s) ?

=DATEVALUE()

=TIMEVALUE()

=MID()

Put this formula:

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

First, the formula will get the date DATEVALUE:

• By starting with the day MID(B2,1,2)
  • 1 is the first character in the cell B2
  • 2 is the number of characters I want it to display
  • Result = 01
• Then with the month MID(B2,4,2)
  • 4 is the fourth character in the cell B2 (the dot “.” is counting as a character)
  • 2 is the number of characters I want it to display
  • Result = 02
• And lastly, the year MID(B2,7,4)
  • 7 is the seventh character in the cell B2 (the space also as a character)
  • 4 is the number of characters I want it to display
  • Result = 2019

Secondly, the time TIMEVALUE:

• MID(B2,12,5)
  • 12 is the twelfth character in the cell B2
  • 5 is the number of characters I want it to display
  • Result = 11:12

And last thing, don't forget to format the cell if not it will display a decimal number.