Clever Geek Handbook

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

Can anyone please help? I am assigned a time value J2000

. Using EXCEL formulas, I need to convert it to normal date and time format like this:

  • Example: given J2000 time = 430969629.999

  • The answer should be: 08/28/2013 and 13:47:10

    (wartime).

Does anyone know the EXCEL algorithms or formulas to get it?

+3


source to share


3 answers


If the J2000 time value is specified as seconds pm 01/01/2000 then the following should work:

enter image description here

Formula in B2 



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

can be copied down to B3

.

The format of the number in A2:A3

is mm/dd/yyyy hh:mm:ss

.

+3


source


Cell A1 contains:

430969629.999

Cell A2 is in date format and contains:



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

Cell A3 has a military time number format:

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


source


This is great information. Thanks to everyone who contributed. This is for those having trouble finding the format in Excel so that the numbers are displayed correctly.

In MS Excel 2013 for military time date and time:

  • Select the cell you want to change for
  • Right-click and choose Format Cells from the pop-up menu
  • Select "Date" in the "Category" column
  • Scroll down the Type column and select an example that shows the date and time in military format [for my Excel 2013 installation, this was shown on 3/14/12 13:30].

In step 4, you can also choose the 12 hour time as 3/14/12 1:30 pm.

0


source






More articles:


All Articles