HOME  > >  GUIDE TO DATABASE of OPENOFFICE / LIBREOFFICE

An exercise in simple spreadsheet use-
Arising from the 2020 Covid-19 pandemic
Date calculations a feature... "Days since"


---

Filename: covid-dates.htm

When I wrote this, we (the world) were living in Interesting Times.

I've written elsewhere to suggest that keeping a journal might be a good idea on various grounds, and tried to make suggestions about how it might be done.

In this essay, I am going to be throwing a stone at these two birds...

If you don't like computers, at least get an appointments diary, and write down things on the day they occurred. I started my own "diary" of my experience (in England) on 20 Mar 20, and was amazed at how many dates I'm already unsure about! Years from now, I won't, without my diary and spreadsheet, even remember that for a time I was worried, "Will my friend's big birthday party go ahead?", and certainly not when that party was to have been, when it was postponed.

-----

Make your spreadsheet how YOU want it, but I'd suggest at least the following columns. You will be shown how to set it up, and some general matters of "good practice" will be covered along the way.

screenshot of spreadsheet with record of Covid-19 events

At top... a preface section, with general notes about the spreadsheet.

Then a big table, with the following columns. The "M" or "A" at the start of each entry is for "M"anual- things you type in by hand, and "A"utomatic-

(M) Date. As on a calendar. I believe the first cases of an unusual pneumonia were identified on 29 Dec 19. (If you can help with a citation of a reputable source for that number, I would be grateful if you contacted me, subject line "When it started".)

(A) "World's day". A number. If the first cases were noticed on 29 Dec 1, then that is "World Day 0".

(A) "My Day". A number. For many people, their concern over Covid-19 did not begin with the first case. Pick a day that was the "first day" for you. The date that Covid-19 began harshly to affect you directly.

(M) "Event". Text, e.g. "Fred heard that a co-worker is confirmed to have it", or "Italy's reported deaths passed China's" (19 Mar 20)

-----

You will be shown how to set up a spreadsheet that will calculate the two day numbers automatically.

This essay tells you exactly what to do in OpenOffice's free spreadsheet "Calc". The spreadsheet in LibreOffice is extremely similar. Both are available free for Windows, Mac, Linux.

Nothing arcane is used. Any spreadsheet should be capable of what is suggested here.

-----

Start a new spreadsheet.


There's a bunch of mundane stuff for a bit. Things get more advanced before too long, though.

Follow your usual practices for standard things.

If you don't have any "usual practices", it is a good idea to...

Put the filename somewhere in the spreadsheet. The upper left cell (A1) of the first page is a good place to put it.

I hope you will building the spreadsheet on your computer as we go along. It really does help you learn from this sort of essay.

Put a version identifier on the spreadsheet. Cell A2 on the first page is a good place for it. The date makes a good ID. Add a letter if several versions arise in a single day. "12 Dec 91" would be the first version of that day, "12 Dec 91-b" would be the second. "-b". Not "-a". A letter-less date is, if you follow my practice, assumed to be "(that date)-a".

If your spreadsheet becomes multi-page (this one doesn't), it is a good idea to have A1 and A2 fill from page 1 onto every other page.

I don't know that it matters much, but I find it amusing to have a note of when I started something on everything I do. This tends to be in A3 of the first page of my spreadsheets. (A3 on other pages usually has the page's ID.)

Leave five or six blank lines. They'll be used before you know it!

Then do the column headings....

Date // World's day // My day // Event

... only don't do them like that! In the "World's day column, it is very unlikely that more than "999" will appear. So don't make the heading so wide.

Better...

Date // World's // My   // Event
// day // day //

Put the first line in cells of row 6. (It could go a bit further up or down the page, but for the sake of this exercise use exactly the cells I specify. It will make things easier later.)

Now go down the spreadsheet and select cells A30 to F30.

Right-click in the selected area and choose "Format cells..."

Select the "Background" tab, and select a color you like.

We are creating a "bar" on the sheet. We will put the text "End of prepared area" on that bar. When we format cells in a moment, we will do all cells down to that bar. The bar lets us know which cells have been formatted, what haven't.

Save what you have done. This won't be your first save, will it? Save often! (Or weep from time to time. We've all been there, haven't we?)

Select cells A8-A29. Right-click. Format cells. "Numbers" tab.

Set these cells to display "Date" data, in the format that gives, say 31 May 99. (If that's not in your menu of formats, just put DD Mmm YY into the "Format Code" box. Once you've done that, it will be available to you in the future as a user-defined code.) Or use a different date format, if you wish. But it is helpful if the data in a field uses horizontal space efficiently.

The default formatting will be fine for the other columns.

====

In row 8, put 29 Dec 19 for the date, zero (0) for the "World's Day", and "Four cases of unusual pneumonia noticed." as the event.

(I am "counting from zero". You can change this to count from 1 if you would rather.)

Skip down to row 14. (You can insert or delete blank rows later, to make new "space" available, as needed.)

Enter 19 Mar 20 for date, "Reported deaths Italy passes reported deaths China." as the event.

The word "reported" may or may not be important here. But that did happen on that date. Remember that the population of Italy is 62 million (CIA Factbook, '17) and the population of China 1,380 million. (23x the population of China. and China had had the disease for much longer than Italy.) Hmm. Maybe living in a democracy, where the government cannot be as dictatorial, has a price? I've also heard it said that the Chinese people are, to a greater degree than people in Western Europe, happy to trust their government and accept the government decisions. I wonder if The UK's PM would have closed theatre sooner if he didn't fear opposition? Or was it that he didn't want the claims for compensation? Much food for thought in this virus thing!

The first fancy bit...

If September 11 is "Day 0", what day is September 13th? Day 2, right? 13-11=2! It isn't hard. If you care about EXACTness, you might have noticed an oddity, though... the 13th is the THIRD day if the 11th is the 1st. This hints at something called the fencepost error, which can be a real headache for programmers. (Counting from zero helps, which is why they do it!)

But I digress!

Coming back to dates... what "day" is November 11th, if September 11 if "Day 0"? (Yes... "about" 60... but you could only say that quickly because I gave you nice dates to compare. And I don't want approximate numbers.)

Spreadsheets do something helpful: Internally, dates are just numbers.

When a spreadsheet cell is formatted to use it for dates, it makes the spreadsheet convert the internal number into something like 11 Sep 2001.. or maybe "September 11th, '01"... many formats are available.

Formatting a cell to be used for dates also affects how input is interpreted. Put 9/11/01 into a spreadsheet, and it may be interpreted as September 11... or as 9 November! Dates give me headaches.

But! We don't have to worry about ALL of that.

Into B14, put...

=A14-A8

You should see 81. The day Italy took the lead was day 81.

Now... the formula "=A14-A8" is all well and good for cell B14.

(Before we go further, put "20 Mar 20" into A15.)

And you can copy formulae, and adjustments are made for you. Copy B14 to B15... And look at the formula that get's entered: =A15-A9. The "A15" is good! The "A9" is not... we need "A8" there.

Here's the fix: Go to A14, and change the formula there to...

=A14-$A$8

... and try the copy/paste again. Ah ha! "Better"! (Try changing A15 back to nothing. Woops! See why I had you put the date in first? Putting the date in after does no harm, but the affect on B15 might have confused you!)

The dollar signs change A8 into something that is not "corrected" when you do a copy/paste. This can be very handy. (You can put the $ on just the letter, or just the number, if you want one of them, only, corrected during pastes. And you will sometimes want to do that... when you are building fancier spreadsheets.)

A shortcut: If you are editing a cell reference, just pressing shift-F4 will change the reference for you.

Onward...

Finish line 15 as follows, and put the next data on line 16....

Date            Event
20 Mar 20   UK schools close
25 Apr 20    Who knows?

Put 0 (zero) in for "My Day" for 20 Mar 20.

Use copy/paste to fill in the "World's Day" column for these new entries.

Do a formula for the "My Day" entry for 25 April. You should get 36.

If you can't get it working for yourself, highlight everything...
from HERE XXX...

The formula would be =A16-$A$15 if your "My Day Zero" was on line 15.

... to HERE XXX.

(Some writing should appear between the "HERE"s. See http://www.arunet.co.uk/tkboyd/hh4b.htm, if you liked that trick, and make your own web-pages.)

That's about it.

That's all there is to this simple spreadsheet for recording the events that you want to remember. Now you just have to find the discipline to actually make the entries!

A little tidying up is needed... Adjust the widths of the columns. Consider setting things so that the numbers in "World's Day" and "My Day" are centered in their columns. Details. But not complicated to make "right".

A trick: If you want to put a hyphen ("-") in the "My Days" cells for dates before your Day 0, put a space in front of it. Otherwise things get all complicated because the spreadsheet thinks you're starting a formula.

Bonus points!

The easy thing to do would be to fill the whole "World's Day" and "My Day" columns with formulae in every cell, from the outset.

However this would lead to many meaningless and unsightly entries. They would arise on every line where there is no entry in column A.

One rather tedious alternative is to copy/paste the formulae as you go along.

The fancy answer is to use a conditional.

I always get muddled with conditionals, so I build them as follows...

Put a 5 in B20.

Look up the syntax of the "IF" statement... you should get something like...

IF(test; value1; value2)

The first "gotcha" is that we need to know that you put an equals sign at the start of anything that is going to be a formula.

Put the following in C20...

=IF(B20>4;">four";"<five")

Pay close attention to, as one of my pupils once said of decimal points, "Dem damn dots, man."

If you get that right, then C20 should report on the value in B20.

The three parts of the "IF" statement are...

What's that got to do with our spreadsheet for the Covid events??

Put 26 Apr 20 in A21. Put the following in B21....

=IF(A21>0;=A17-$A$8;"")

(That's two "quote marks" in a row, just before the final ")".) (Speaking of complex punctuation!)

You should get 119. (Which "fits" what 25 Apr 20 gave on row 17, doesn't it?)

I've almost always eschewed such tricks. It seems to give the computer a lots of perhaps pointless work. But that's a trick you may want to use, or to know, to use elsewhere.

A detail...

As it stands, our spreadsheet has some blank rows.

They are there for filling in things that interest you which happened before you came to this.

If you want to remove any of them, you can. The formulae will adjust themselves; you won't be left with a mess. (Anyway... you will save a backup copy of how it was before you got adventurous (in case the wheels came off) didn't you?)

Just right-click on the row's number, over at the left, and "Delete rows" is one of your options. (To delete several rows, select several by dragging across the row numbers before you right-click.)

You can add rows a similar way.

Right-click on the number marking row 15.

Chose insert rows. A NEW row 15 will be inserted. What was there is now in row 16, and everything below was neatly pushed down. (Again, you can insert multiple new rows. To insert three rows, select three rows before doing the "insert".)



I hope...

I hope you will try this. In years to come, you might enjoy a record of your experiences at this complicated time. In any case, if it helps you use spreadsheets more cleverly, that's good all by itself.

I hope, maybe, you've even "enjoyed" wrestling with this? I haven't particularly "enjoyed" writing it... but I did it in hopes of one or more of the above for you.

There's another Covid related idea for you as part of my Flat Earth Academy.

Your thoughts always welcome. If you aren't 18 yet, please have someone else get in touch by email on your behalf. Please cite "SheGui/fss/covid-dates.htm".

Oh!

Oh! Nearly forgot... you can download a copy of the spreadsheet developed during this exercise. Try to build your own, from scratch. But if you just can't the download may help?






Search across all my sites with the Google search button at the top of the page the link will take you to.
Or...

Search just this site without using forms,
Or... again to search just this site, use...

Powered by FreeFind

Site search Web search

The search engine merely looks for the words you type, so....
  *!  Spell them properly   !*
  Don't bother with "How do I get rich?" That will merely return pages with "how", "do", "I", "get" and "rich".

I have other sites. My Google custom search button will include things from them....

   One of my SheepdogGuides pages.

   My site at Arunet.


Ad from page's editor: Yes.. I do enjoy compiling these things for you... I hope they are helpful. However.. this doesn't pay my bills!!! If you find this stuff useful, (and you run an MS-DOS or Windows PC) please visit my freeware and shareware page, download something, and circulate it for me? Links on your page to this page would also be appreciated!



This page's editor, Tom Boyd, will be pleased if you get in touch by email. Suggestions welcomed! Please cite "SheGui/fss/covid-dates.htm".

Valid HTML 4.01 Transitional

Page has been tested for compliance with INDUSTRY (not MS-only) standards, using the free, publicly accessible validator at validator.w3.org. Mostly passes.

AND passes... Valid CSS!

-- Page ends --