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.
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: [$-en-US]mmm d, yyyy;@ --> 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.
Attached below, the chart that Microsoft Support posts (follow the link for more)
Add Comment