In EXCEL how do I convert J2000 time value to standard date and military time?

Question

Can someone please assist? I am given a J2000 time value. Using EXCEL formulas, I need to convert it to a normal date and time format like this:

1. Example: Given J2000 time = 430969629.999
2. Answer should be: 08/28/2013 and 13:47:10 (military time).

Does anyone know the algorithms or EXCEL formulas for getting it?

Answer 1

If the J2000 time value is defined as seconds after noon 01/01/2000, then the following should work:

Formula in B2

=DATE(2000,1,1)+TIME(12,0,0)+A2/60/60/24

can be copied downwards to B3.

Number format in A2:A3 is mm/dd/yyyy hh:mm:ss.

Answer 2

Cell A1 contains:

430969629.999

Cell A2 has the Date number format and contains:

=A1/(60*60*24)+"1/1/2000"+"12:00"

Cell A3 has the military Time number format contains:

=A1/(60*60*24)+"1/1/2000"+"12:00"

Answer 3

This is great info. Thanks to everyone who contributed. This is for anyone who had trouble finding the format in Excel to make the numbers display properly.

In MS Excel 2013, for the date and time in military time:

1. Select the cell you wish to change the format for
2. Click right mouse button and select "Format Cells" from the popup menu
3. Select "Date" from the "Category" column
4. Scroll down on the "Type" column and select the example that shows a date and time in military format [for my install of Excel 2013, it showed 3/14/12 13:30].

In step 4 you can also select a 12 hour clock time as 3/14/12 1:30 PM.