Log in
Skip to sidebar
Skip to main content
Spaces
Create
Create
Hit enter to search
Help
Online Help
Keyboard Shortcuts
Feed Builder
What’s new
Available Gadgets
About Confluence
Log in
People
Serena Barbieri
Blog
2022
November
23
Frustrating Date Function in Excel: Excel has an Issue with Historical Dates
Frustrating Date Function in Excel: Excel has an Issue with Historical Dates
search
attachments
weblink
advanced
image-effects
image-attributes
Paragraph
Paragraph
Heading 1
Heading 2
Heading 3
Heading 4
Heading 5
Heading 6
Preformatted
Quote
Bold
Italic
Underline
Colour picker
More colours
Formatting
Strikethrough
Subscript
Superscript
Monospace
Clear formatting
Bullet list
Numbered list
Task list
Outdent
Indent
Align left
Align center
Align right
Page layout
Link
Table
Insert
Insert content
Files and images
Link
Markup
Horizontal rule
Task list
Date
Emoticon
Symbol
Insert macro
User mention
Info
Status
Gallery
Table of Contents
Other macros
Page layout
No layout
Two column (simple)
Two column (simple, left sidebar)
Two column (simple, right sidebar)
Three column (simple)
Two column
Two column (left sidebar)
Two column (right sidebar)
Three column
Three column (left and right sidebars)
Undo
Redo
Find/Replace
Keyboard shortcuts help
You are not logged in. Any changes you make will be marked as
anonymous
. You may want to
Log In
if you already have an account.
This page is also being edited by
. Your changes will be merged with theirs when you save.
<p>This is an update to my November 21, 2022 <a class="confluence-link" href="/confluence/pages/viewpage.action?pageId=49479930" data-linked-resource-id="49479930" data-linked-resource-version="3" data-linked-resource-type="blogpost" data-linked-resource-default-alias="About date formatting in Excel" data-base-url="https://wiki.rice.edu/confluence">post</a>. </p><p>Unfortunately, after several unsuccessful tries, I discovered that the function MAX (and any other function) does not work in Excel for dates before 1900. In summary, Excel reads dates as numbers, and it is created to read dates between 1900 and 9999. It's so incredibly useful to have 9999 and not 1899 (sarcasm). The program does not work well for analyzing historical dates written in the format "mmm dd, yyyy" and ANY other format with a year value of less than 1900. According to several blogs, there is a way around it: Transforming the year by adding 1000 (or 2000 according to others for some issue regarding leap years that I refuse to try to understand). In this way, 1876 becomes 2876 or 3876. Now, for my specific purpose (finding the more recent days among a series and automatically pasting it in a specific cell) this is too much of a complication. </p><p>So, now, not only I did not save any time in building my database but I even lost 5 hours trying to figure out it was not possible. Nevertheless, it was worth trying.</p><p>In the past, I used historical data in my databases (the 900 members of the labor union in Galveston, and the 5,000 deserters among Connecticut Union soldiers), but at that time I was using Numbers (Mac spreadsheet) instead of Excel. I wonder if I should use Numbers and then save the database as an Excel file. I will make some tests over the Thanksgiving break. </p>
If you are unable to use this CAPTCHA please <a href="administrators.action" tabindex="5">contact your administrator</a> for assistance.
Edit
Save
Close
Preview
View changes
Revert to last published version
{"serverDuration": 52, "requestCorrelationId": "1db34bb3f5d79964"}