tel 01732 833085 · e-mail david wallis
I need a formula in Excel that will convert a data and time into a military data/time format that includes the time zone letter indicating the UTC time zone offset.
The date appearing an Excel cell as dd/mm/yyyy HH:MM is required in the military form ddHHMMZmmmmyy. The key to these forms is this:
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 this date is 12 July 2010):
To convert this input into the required military date, input this formula into 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 this result 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
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.