dmw | consultancy
tel 01732 833085
tonbridge · kent · UK

Excel Military Zulu Time UTC
Date Time Conversion

A format and a formula each of which will convert a data and time in Excel into a military data/time format that includes the time zone letter indicating the UTC time zone offset.

Last updated on 2018-08-01 by David Wallis.


Background

The date appearing an Excel cell as dd/mm/yyyy HH:MM is required in the military form ddHHMMZmmmyyyy. The key to these forms is this:


Solution

Consider this input in cell A1 of the spreadsheet (on a PC with its regional options date/time set to dd/mm/yyyy format so that a date of 12 July 2018) is displayed as 12/07/2018 14:15.

Using a custom format of ddhhmm"Z"mmmyyyy will display the date/time as 121415ZJul2018, but I understand that sometimes the format requires that JUL.

I’ve been unable to create a custom format that will capitalise the Jul, so to achieve this I have used this formula in cell B1:

=CONCATENATE(TEXT(DAY(A1),"00"),TEXT(HOUR(A1),"00"), TEXT(MINUTE(A1),"00"),"Z",CHOOSE(MONTH(A1),"JAN","FEB", "MAR","APR","MAY","JUN","JUL","AUG","SEP", "OCT","NOV","DEC"),TEXT(YEAR(A1),"00"))

That formula yields the result 121415ZJUL2018 in cell B1.

Note that the formula is one long one with no spaces. So if you are copying it into Excel, make sure that no spaces appear in the formula and that the formula is not split across more than on cell


Disclaimer

DMW Consultancy Ltd does not accept any liability for loss or damage to data to which any techniques, methods or code included in this website are applied. Back up your data; test thoroughly before using on live data.

Convert 12/07/2018 14:15 into 121415ZJul2018 or 121415ZJUL2018