Using choose() for timesheets

Traditionally, many businesses created timesheets as blank forms with the days grouped into weeks, and employees were expected to fill in the dates, times, and other relevant information. In this situation the employee is the one responsible for getting the correct dates on the correct days of the week. The employee was also often responsible for adding up the hours, though of course any sensible employer would spend some time double-checking the addition.

Nowadays, with email and the Internet making it easy to transmit and store electronic documents, some companies have switched to having employees enter their hours into a spreadsheet — often the same spreadsheet that had previously been printed out and photocopied. Sometimes, however, employers will go to the effort of having the form pre-populated with dates, and if they do that then choose() can be helpful.

I made an example timesheet that does exactly that, and a few other nifty things besides.

  • The date that it was filled out/printed is automatically calculated using =today(). If you want to see how it would look in different months, just overwrite this date. [Cell F26]
  • The month covered by the timesheet is calculated, using the previous result and date(), year(), and month(). [Cell I3, named startingdate]
  • The 1st is placed on the correct day of the week, and all previous dates are shown as blanks, using choose(), mod(), and if(). [Cells A7:A13]
  • All of the dates after the end of the month are shown as blanks, using if(). [Cells E17:E23, H17:H23]
  • Hours filled in before the beginning or after the end of the month are pointed out, using if(). [Cells C7:C13, F17:F23, G17:G23]

In addition to the various formulas, I also did a little formatting to improve readability.

  • Blank lines for the name, ID#, and signature were created using bottom borders.
  • The total for each column is separated from the daily hours using a bottom border.
  • The total for the month is set off and easy to spot, because it’s bout an outside border.
  • The before/after error messages are easy to spot as not belonging in the spreadsheet, because they’re a different font size.

If you look at the choose() statements in A7:A13, you’ll notice that they’re rather long, and that they’re all different. Here they are again, so you can see them all at once.
Sun=choose(if(mod(startingdate,7)=0,7,mod(startingdate,7)),startingdate,,,,,,)
Mon=choose(if(mod(startingdate,7)=0,7,mod(startingdate,7)),A7+1,startingdate,,,,,)
Tue=choose(if(mod(startingdate,7)=0,7,mod(startingdate,7)),A8+1,A8+1,startingdate,,,,)
Wed=choose(if(mod(startingdate,7)=0,7,mod(startingdate,7)),A9+1,A9+1,A9+1,startingdate,,,)
Thu=choose(if(mod(startingdate,7)=0,7,mod(startingdate,7)),A10+1,A10+1,A10+1,A10+1,startingdate,,)
Fri=choose(if(mod(startingdate,7)=0,7,mod(startingdate,7)),A11+1,A11+1,A11+1,A11+1,A11+1,startingdate,)
Sat=choose(if(mod(startingdate,7)=0,7,mod(startingdate,7)),A12+1,A12+1,A12+1,A12+1,A12+1,A12+1,startingdate)

The first part of the choose() statement is the selection criteria – in this case it’s:
if(mod(startingdate,7)=0,7,mod(startingdate,7))

That looks complicated, but the idea behind it is simple. =mod(startingdate,7) would return a number between 0 and 6. However, choose() needs the all the numbers to be 1 or greater. So we could just add a 1 to what it returned, but that would mean that Saturday=1, Sunday=2, Monday=3, and so on. It’s not very intuitive, and that means it’s easy to mess up the rest of the formula. I decided that it was better to make the first part (the part that stays the same) more complicated in order to keep the rest of the formula (the part that changes) more straightforward.

So instead of making the selection critera mod(startingdate,7)+1, I said that IF mod(startingdate,7) was equal to 0, then the selection criteria should be 7, and otherwise it should just be the regular mod(). That means that Sunday=1, Monday=2, and so on.

After that, things are different in each formula.
In the first one, if the selection criteria is 1, meaning the month started on a Suday, then this cell is equal to the starting date. Otherwise it’s blank.
In the second one, if the selection criteria is 1, then the cell is equal to the the cell above it plus one. That’s because if it started on Sunday, then Monday is day 2. If the criteria is two (i.e. the month starts on a Monday), then it’s equal to the starting date. Otherwise, it’s blank.
In the third one, if the criteria is 1 or 2, then it’s equal to the cell above plus one, if the criteria is 3 then it’s equal to the starting date, and otherwise it’s blank.
And so on.

As with any set of long, slightly varied formulas, concatenate() can make it easier to create them without errors. On Sheet2 I used a series of concatenate() functions, combined with row(), to specify what the decision should be for each option [Sheet2!C4:I10)]. The row() meant I didn’t have to manually update which row each formula was supposed to refer to when I copied the formula down.

I then used concatenate() again in a set of incremental formulas that compiled all of the options together [Sheet2:J4:Q10]. In the first column I just concatenated the first column of options, and a comma. Then in the remaining columns I took the previous column’s results, and added on the next option and another comma. In the last column of that set I didn’t add on a final comma, but if I had it wouldn’t have hurt anything.

Finally in column Q I added the parts of the formula that don’t change: “choose(if(mod(startingdate,7)=0,7,mod(startingdate,7)),” and “)”.

Then I copied the results of the concatenate() formulas to A7:A13, added the equal sign at the start, and the formulas were done.

Could I have done this without choose()? Absolutely.
=if(mod(startingdate,7)=1,startingdate,””)
=if(mod(startingdate,7)=2,startingdate,if(startingdate,7)>2,””,A7+1)
=if(mod(startingdate,7)=3,startingdate,if(startingdate,7)>3,””,A8+1)
=if(mod(startingdate,7)=4,startingdate,if(startingdate,7)>4,””,A9+1)
=if(mod(startingdate,7)=5,startingdate,if(startingdate,7)>5,””,A10+1)
=if(mod(startingdate,7)=6,startingdate,if(startingdate,7)>6,””,A11+1)
=if(mod(startingdate,7)=0,startingdate,A10+1)

Which is better? That entirely depends on which makes more sense to you. The if() is certainly more compact, but the choose() makes it easier to lay out the possibilities if you’re the sort that likes to see everything written out in order to make sense of it.