Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Jos Jos is offline
external usenet poster
 
Posts: 7
Default permanent conversion of 1904 date format to 1900 date format

I have an old spreadsheet in Windows that has the 1904 date format as basis.
All my other spreadsheets are in the 1900 date format. Are there any
utilities that can permanently convert a spreadsheet from the 1904 to the
1900 date format?
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,231
Default permanent conversion of 1904 date format to 1900 date format

Jos wrote...
I have an old spreadsheet in Windows that has the 1904 date format as basis.
All my other spreadsheets are in the 1900 date format. Are there any
utilities that can permanently convert a spreadsheet from the 1904 to the
1900 date format?


There's no such thing as permanent conversion.

I'm not aware of any utilities that would do this, but it's fairly
easy to use a macro to do this. Basically, the macro would open a
workbook, check if the workbook is using the 1904 date system, and if
so change it to the 1900 date system and iterate through all used
cells in all worksheets checking for cells containing constant dates
(the cell's .HasFormula property would be FALSE while its .Value
property would be type Date and have value 1), and when constant
date values were found, add 1462 to them.
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 22,906
Default permanent conversion of 1904 date format to 1900 date format

I don't know of any utilities but subtracting 1462 from each 1904 date will
give you a 1900 date.

Helper column with =A1-1462 will convert.

Or enter 1462 in an unused cell.

Copy that cell then select the range of date cells and Paste
SpecialSubtractOKEsc.


Gord Dibben MS Excel MVP

On Mon, 20 Jul 2009 16:32:05 -0700, Jos
wrote:

I have an old spreadsheet in Windows that has the 1904 date format as basis.
All my other spreadsheets are in the 1900 date format. Are there any
utilities that can permanently convert a spreadsheet from the 1904 to the
1900 date format?


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,231
Default permanent conversion of 1904 date format to 1900 date format

Gord Dibben <gorddibbATshawDOTca wrote...
I don't know of any utilities but subtracting 1462 from each 1904 date will
give you a 1900 date.


You want to *ADD* 1462 to 1904-system dates to make them 1900-system
dates.

Think about it for a minute or two.

Then try testing.
  #5   Report Post  
Junior Member
 
Posts: 1
Default

I had the same problem and I did not find any solution on internet.
So I wrote this procedure.

Sub Y1904()

For Each Sht In ActiveWorkbook.Sheets
Set Rng = Sht.UsedRange
For Each cell In Rng
If cell.HasFormula = False Then
If IsDate(cell.Value) Then
cell.Value = cell.Value + 1462
End If

End If

Next
Next
End Sub

Basically it adds to all cells containing a date (not formula) the 1462 value.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
1900 vs 1904 date functions Mary Lou Excel Worksheet Functions 1 October 27th 08 04:43 PM
convert Excel sheet in 1904 format to 1900 without changing dates Joe Excel Worksheet Functions 1 June 8th 07 01:51 PM
Date format in permanent list kayabob Excel Discussion (Misc queries) 1 October 14th 06 09:35 AM
Set Permanent Date Format to MM//DD/YYYY kayabob Excel Discussion (Misc queries) 1 July 7th 06 04:46 PM
Date format pre-1900. Craig & Co. Excel Discussion (Misc queries) 1 February 15th 05 06:09 AM


All times are GMT +1. The time now is 09:09 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"