When it comes to manipulating data on Mars, one of the most difficult problems that we often come into is the inconsistencies in timekeeping standards in NASA’s data. Perhaps the most difficult of these is converting between UTC and MTC timers in order to calculate the significance of certain types of data such as temperature and pressure in correlation with the time of day they are found at. Below, we will show you how to calculate MTC in Excel in order to overcome this.
Calculating MTC
Step 1) Create a Date/Time in an excel cell and rename the cell to “UTC”.
Step 2) Insert the following formula into another cell:
=TIME(FLOOR(MOD(((((UTC*86400 - (DATE(1970,1,1)*86400))/86400+2440587.5+(67.184/86400)-2451545-4.5)/1.027491252)+44796-0.00096)*24, 24),1),FLOOR(MOD(((((UTC*86400 - (DATE(1970,1,1)*86400))/86400+2440587.5+(67.184/86400)-2451545-4.5)/1.027491252)+44796-0.00096)*1440, 60),1),FLOOR(MOD(((((UTC*86400 - (DATE(1970,1,1)*86400))/86400+2440587.5+(67.184/86400)-2451545-4.5)/1.027491252)+44796-0.00096)*86400, 60),1))
This will then return the time in Mars Coordinated Time.
Calculating MSD
Step 1) Create a Date/Time in an excel cell and rename the cell to “UTC”.
Step 2) Insert the following formula into another cell:
=((UTC*86400 - (DATE(1970,1,1)*86400))/86400+2440587.5+(67.184/86400)-2451545-4.5)/1.027491252+44796-0.00096
This will then return the time in Mars Solar Date.
You can download an excel file with examples of each here: UTC/MTC Formula
Remember, you will still have to correct for the Local Mean Solar time of whatever destination your data is collected from. This information is generally available from the Wikipedia pages of each individual mission.
