Converting hourly Excel time to Igor time shift

Share icon

Tue, 02/14/2023 - 01:10 am

Hi everyone,

I have been using the conversion formula to convert Excel time to Igor time from the Help center:

wave *= 24*3600 // Convert days to seconds
wave -= 24*3600*365.5*4 // Account for four year difference
wave = round(wave)

However, I always have a problem when converting hourly data because it does not convert the data exactly on the hour (there is an excess about 2 or 3 seconds, ahead or behind, that is unsolvable with the round function) as the following example:

23-Sep-22 01:00:03
23-Sep-22 01:59:57
23-Sep-22 03:00:00
23-Sep-22 04:00:03
23-Sep-22 04:59:57
23-Sep-22 06:00:00
23-Sep-22 07:00:03
23-Sep-22 07:59:57
23-Sep-22 09:00:00
23-Sep-22 10:00:03
23-Sep-22 10:59:57
23-Sep-22 12:00:00
23-Sep-22 13:00:03
23-Sep-22 13:59:57
23-Sep-22 15:00:00
 

It may not be a problem when I want to plot it as time series, but quite annoying when doing diurnal plots. For instance, the time of 01:00:03 and 01:59:57 are both included in the same hour, and it repeats the same way over the course of the time, and therefore leaving diurnal plot at 2:00 (also 05:00, 08:00, 11:00, etc.) zero because it does not recognize any data at that hour. So far, to resolve this issue, I just added 3 seconds to my time series. Has anyone faced the same issue? Thank you!

It is a bit difficult to judge where this (rounding?) error might come from without further information. I assume you load some excel file into Igor and then apply the posted formula. I do not see how this introduces an error. I suspect that the error is already intrinsic in the excel-provided data, gets introduced when loading the data with limited fidelity or due to some other problem with limited data fidelity in Igor itself. Would it be possible to provide some data (e.g., an Excel file) here and exactly describe the steps you are doing to end up at above result?

If the 3 second variation is already intrinsic to your data, and you know that you only have data on the full hour (i.e., minutes and seconds are irrelevant) then you could simply round to full hours and be done. Something like

round(timedata/3600)*3600

should do (I haven't tested this, though).

February 14, 2023 at 03:43 am - Permalink

I load the numeric date values from Excel (csv) below into Igor then apply the formula.

Additional info: this does not happen when the time series have 10-minute or 1-minute resolution (or maybe unnoticeable). Would it happen because the wave is scaled differently in Igor when you have different resolution? I use the Change Wave Scaling menu to turn the data from "Numeric" to "Date&Time".
date.csv

February 14, 2023 at 04:07 am - Permalink

I don't think it's a matter of conversion. Excel shows the same 2 to 3 sec offset with the data above.

February 14, 2023 at 04:52 am - Permalink

Wait, I just noticed the offset on the uploaded file, but the original csv file I uploaded is not shifted. I guess it is Excel's error when saving the date data in numeric form as csv file. Thank you everyone! I will look this up from the Excel side.

February 14, 2023 at 05:01 am - Permalink

Looking at your excel data, it gets clear that the time stamps are already off by a few seconds here. The reason is that the time is saved as days, where hours, minutes, seconds are just a small fraction of the total number. You have only a maximum of four digits to work with in the data, but many timestamps are actually repeating decimals. For example you first entry is truncated to .0417 days, but to save the exact hour you would need .04166666666... and so on. Thus, your only option is to save your data with a higher number of trailing digits in the first place or round the values to full hours using my code above. The former would actually need exceedingly precise fractions so I would recommend to use a different format of saving the time if possible (e.g., using seconds).

February 14, 2023 at 05:02 am - Permalink

Make sure that you are using a double-precision wave to store date/time date in Igor. For details, execute:

DisplayHelpTopic "Date/Time Waves"

 

February 14, 2023 at 06:08 am - Permalink