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
21
About date formatting in Excel
About date formatting in Excel
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>After the consultation with Catherine Barber, she came up with the idea of using the function MAX to automatically copy either the date of discharge, pardon, escape, or transfer and paste it as the last date of work at a given work camp. It seems like a minor achievement, but it can amount at some hours when we think of doing the task manually 40,000 times. It is not just the typing time, it is also the scrolling time in a spreadsheet that has columns from A to UM, and retreaving the date in the original document a second time because I cannot trust my memory. Overall, I think I can save about 110 hours by using that little trick (at a rate of 6 dates per minute). That stimulates me to think about more ways I can make my life easier by using excel functions well before the data analysis begins.</p><p>When I applied the formula, I realized that several date columns were improperly formatted (somehow Excel read the dates as text although formatted as dates properly). Not only that caused the formula to fail, but the problem is relevant to me because it prevents other calculations (like time duration of events) that I want to perform when the database is completed. I had to reformat the cells properly, choosing a custom format that allows me to write the dates exactly as they appear in the documents (short month day, year: e.g., Mar 23, 1878). I had to re-learn (bad memory sucks) that Excel uses a code for dates based on the repetition of the basic symbol for day, month, and year–"d," "m," "y"–to make Excel understand which format I want: "d," for example, is for the format 1-31,"dd" is for the format 01-31; while "mmmm" is the format for the full name of the month, "mmm" is the one for the abbreviate name (the one I am using: e. g., Mar and not March), and so on. The format I am currently using is:<strong> [$-en-US]mmm d, yyyy;@ </strong>--> I am writing it here so I won't forget it, and I can apply it again if I introduce new columns on the way.</p><p>Attached below, the chart that Microsoft Support posts (follow the link for more) </p><p><a href="https://support.microsoft.com/en-us/office/format-a-date-the-way-you-want-8e10019e-d5d8-47a1-ba95-db95123d273e">Formatting Dates in Excel</a></p><p><img class="confluence-embedded-image" draggable="false" src="/confluence/download/attachments/49479930/Screen%20Shot%202022-11-21%20at%201.48.01%20PM.png?version=1&modificationDate=1669060271992&api=v2" data-image-src="/confluence/download/attachments/49479930/Screen%20Shot%202022-11-21%20at%201.48.01%20PM.png?version=1&modificationDate=1669060271992&api=v2" data-unresolved-comment-count="0" data-linked-resource-id="49709058" data-linked-resource-version="1" data-linked-resource-type="attachment" data-linked-resource-default-alias="Screen Shot 2022-11-21 at 1.48.01 PM.png" data-base-url="https://wiki.rice.edu/confluence" data-linked-resource-content-type="image/png" data-linked-resource-container-id="49479930" data-linked-resource-container-version="3" title="Serena Barbieri > 2022/11/21 > About date formatting in Excel > Screen Shot 2022-11-21 at 1.48.01 PM.png" data-location="Serena Barbieri > 2022/11/21 > About date formatting in Excel > Screen Shot 2022-11-21 at 1.48.01 PM.png" data-image-height="781" data-image-width="811"></p><p> </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": 108, "requestCorrelationId": "4d1c0b48970f48cf"}