« Query for recent files via command line in Ubuntu | Main | Removing autocomplete fields from Housing application »

## September 26, 2012

### Convert Igor Pro date/time to Excel data/time

To see supporting documentation in Igor Pro use this command:

DisplayHelpTopic "Excel Date/Time Versus Igor Date/Time"

Excel stores date/time information in units of days since January 1, 1900 or January 1, 1904. 1900 is the default on Windows and 1904 is the default on Macintosh. Igor stores dates in units of seconds since January 1, 1904.If the Excel file uses 1904 as the base year, the conversion is:

wave *= 24*3600 // Convert days to secondsIf the Excel file uses 1900 as the base year, the conversion is:

wave *= 24*3600 // Convert days to seconds

wave -= 24*3600*365.5*4 // Account for four year difference

This equation will convert to the Excel date/time for Windows systems (1904)

=(H1+24*365.5*3600*4)/(24*3600)

Where H1 is the Igor Exported date/time code. For example, 2954750400

should convert to 35660.5 both of which are 8/18/1997 12:00.

Bonus! Convert the Excel date/time to text string: =TEXT((A2+24*365.5*3600*4)/(24*3600), "yymmddhhmmss")

Once you have a date/time code setup as yymmddhhmmss, how do you take it to an excel date/time?

=INT(DATE(LEFT(B1,2),MID(B1,3,2),MID(B1,5,2))) + MOD(TIME(MID(B1,7,2),MID(B1,9,2),RIGHT(B1,2)),1)

Posted by kkwaiser at September 26, 2012 10:34 AM