I receive historical rainfall data in MS Excel from a state agency and I take the new data and add it to my master spreadsheet, but when I do this, the dates/times get screwed up

It seems like the agency's sheet is using a different time reference... anyway to change this? My sheet is using 1/1/1900 00:00:00 (value=1) as the reference while the agency is using 01/01/1904 00:00:00 (value=0) as the reference.

I don't know what version the agency is using... looks like <'03, possibly '97.

Basically, I need the D/T that the agency sheet gives, which requires me to manually adjust these dates/times in my sheets to reflect the proper date/time reference.

Time since 1900        Agency D/T          My D/T
38895.00                 06/28/10 00:00     06/27/06 00:00
38895.04                 06/28/10 01:00     06/27/06 01:00
38895.08                 06/28/10 02:00     06/27/06 02:00

10/26/2010 11:04:11 AM

I think you should try adding 4 years to the date.

10/26/2010 11:36:08 AM

yeh yeh yeh. I can manually add 4 years and a day to the date... but because I have to do a lot of these and quite often, I'd rather see if there is a setting or something I can do.

10/26/2010 11:39:15 AM

the agency is running a Mac. You have to convert the date system to 1904 for the workbook you're using. You can do that by clicking the excel button, selecting excel options, and under advanced - change the date setting to 1904. I don't know how you do it for versions of excel older than 07.

[Edited on October 26, 2010 at 12:25 PM. Reason : if you are linking workbooks instead of copy/pasting the data, you have to manually adjust the dates]

10/26/2010 12:23:11 PM

ah, ok thanks. I'd rather keep my workbooks on the correct reference (1900). guess I'll just keep updating them manually.

damn mac.

10/26/2010 9:57:16 PM