+ Reply to Thread
Results 1 to 5 of 5

Converting from the 1904 date format to the 1900 date format without losing data

  1. #1
    Registered User
    Join Date
    05-10-2013
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    29

    Converting from the 1904 date format to the 1900 date format without losing data

    So, I came across a spreadsheet that has roughly 6000 lines of information, some of which are dates. Trying to copy it into a new workbook, the dates shifted, and I realized it was a difference in the preference between the 1900 and 1904 year format (since excel uses these to forumlate the date). Is there any way I can change the format from 1904 to the standard 1900, without changing the dates on all 6000 lines, and if not, is there an easy way to bump the date by 4 years for all my entries?

  2. #2
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,255

    Re: Converting from the 1904 date format to the 1900 date format without losing data

    Please Login or Register  to view this content.
    With this formula you get a date that is 4 year later.
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong

  3. #3
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Converting from the 1904 date format to the 1900 date format without losing data

    Or =edate(A1, 48) or =A1+1461
    Entia non sunt multiplicanda sine necessitate

  4. #4
    Valued Forum Contributor
    Join Date
    11-15-2010
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2003
    Posts
    538

    Re: Converting from the 1904 date format to the 1900 date format without losing data

    I'm only hitting this old thread in case anyone lands here from googling the 1904 issue (as in fact happened to me), and in case anyone uses this as a starting point (as I did), I believe that the exact tactic would be to adjust by "4 years plus 1 day" (or 1462 days), rather than 4 years (1461 days). Again sorry to creepily creep into the past, but I did so in case it prevents someone from using this and being just a wee bit inaccurate.

  5. #5
    Registered User
    Join Date
    02-01-2020
    Location
    USA, though I travel to Europe and Asia regularly (and use machines in those locations at our regional offices)
    MS-Off Ver
    Excel 2016 (primarily)
    Posts
    3

    Re: Converting from the 1904 date format to the 1900 date format without losing data

    I just posted about this in a new thread, so sorry about that.

    I want all my workbooks to use 1900 date system, but have some workbooks using 1904. Some of the workbooks using 1904 have 30+ sheets with dates all over the place - not necessarily in a specific row or column.

    For example, I have a sheet with 40 columns, where its 20 pairs of 2 columns for asset and liability values. Each pair of columns has a date in the upper left corner, to specify when those columns were populated with values.

    So the question is ... cells with dates in random places all over the workbook (30+ sheets) and using the 1904 system. We want to convert everything to the 1900 system and simultaneously update the dates accordingly.

    Given this is not organized in the typical way, the formula approach is as efficient as retyping every date.

    EDIT: I get that Excel is a 'spreadsheet' program and that dates 'should' be in clearly marked rows and columns, but unfortunately that is not the situation.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 5
    Last Post: 02-08-2013, 11:06 AM
  2. 1904 Date Format Issue
    By Gregan in forum Excel General
    Replies: 6
    Last Post: 10-28-2010, 11:09 AM
  3. How do I change date format without losing data?
    By ScottNovo in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 04-15-2005, 06:06 PM
  4. [SOLVED] REPOST: Date format pre-1900.
    By Craig & Co. in forum Excel General
    Replies: 2
    Last Post: 02-28-2005, 06:06 PM
  5. Date format pre-1900.
    By Craig & Co. in forum Excel General
    Replies: 1
    Last Post: 02-15-2005, 02:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1