Excel Zulu tid

Hej

en der kan hjælpe mig med at få denne formel oversat til en dansk excel så den virker

Background
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:

dd = the numeric day of the month with a leading zero for the first nine days in the month
mm = the numeric month of the year with a leading zero for January to September
yy = two-digit year; yyyy = four-digit year
HH = the hour of the day on a 24-hour clock, with leading zeroes for 1:00am to 9:59am
MM = the minutes with leading zeroes for the first nine minutes of any hour
Z = Zulu time, ie zero offset from GMT acccording to Universal Time, Coordinated (UTC).
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 this date is 12 July 2010):

12/07/2010 14:15

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:

121415ZJUL2010

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