Microsoft® Excel Military Zulu Time UTC
Date Time Conversion

How to use a formula and formatting to convert a data and time in Excel into a military data/time that includes the time zone letter indicating the UTC time zone offset.

Last updated on 2024-05-03.

Background

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

• dd The numeric day of the month with a leading zero for the first nine days in the month
• HH The hour of the day on a 24-hour clock, with leading zeroes for 1:00 AM to 9:59 AM
• MM The minutes with leading zeroes for the first nine minutes of any hour
• Z Zulu time, meaning zero offset from GMT acccording to Universal Time, Coordinated (UTC)
• mmm The first three letters of the month name
• yyyy Four-digit year.

Solution

Consider this input in cell A1 of the spreadsheet:
July 12th 2018 14:15

Applying to the cell a custom format of ddhhmm"Z"mmmyyyy will display the date/time as 121415ZJul2018.

I understand that sometimes the format requires 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 certain that no spaces appear in the formula and that the formula is not split across more than on cell

Your Support for DMW TIPS

Please support this website by making a donation to help keep it free of advertising and to help towards cost of time spent adding new content.

To make a contribution by PayPal in GBP (£ sterling) —

To make a contribution by PayPal in USD ($ US) —

Thanks, in anticipation.

Disclaimer

David Wallis 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.