Google Spreadsheets Day of the Week

Microsoft Excel is awesome

My mom, on occasion, will babysit for family friends or what have you. She logs the date and time for billing purposes on a notebook. Later when she sends “the bill,” it is sent electronically and yours truly converts that information to an Excel spreadsheet.

I loved using Excel because it is an incredibly powerful software that automates a whole lot of things. All I do is enter the date and start/end times and it will calculate the day of the week and sum up the hours of each day and over whatever period I wish. Excel has many sophisticated functions (like “days of the week”) that in the past were unmatched in the free alternatives (OpenOffice.org which you should now ignore due to Oracle and instead use LibreOffice, and Google Spreadsheets to name a couple), though those programs have caught up quite a bit.

Just as a quick aside, Oracle has now decided they will not commercialize OpenOffice.org but I think we should punish Oracle by ignoring OpenOffice.org and switch over to LibreOffice.

Anyway, I have Office 2007 on my desktop, and installed a copy for my mom on her notebook. However I can’t remember the last time I turned my desktop on. I mostly use the Family desktop or my Acer notebook, but I don’t have an office suite for my little Acer 1410 notebook (Pentium dual-core, 11” screen). At one point in time I was using Office 2010 Beta but that beta ended a while back. Therefore to update her Excel file I always needed to use her computer.

Why I Chose Google Spreadsheets over Excel

So as I said, in order to update/add information into the excel sheet I need to use her computer, or use a computer on the network that has Excel and can access the file in her laptop. Also there was a need to back up the file in the case of some virus or other accident (though each time she sends the bill out it does archive a copy in her Gmail).

My point is I was feeling a bit limited by the idea of using a local file. As most of my data is now in the cloud on Google’s servers (my contacts, calendar, text messages, etc.) and though I backup my Office documents regularly I do wish I can access them anywhere all the time. I do have an option, Microsoft’s Office Live, but why not stick with Google since I’m using Android? (If you’re using a Windows Phone 7 device, you should use Office Live). Also Google Docs is friendly to the Microsoft Office suite, as well as LibreOffice and PDF.

Also my mom had gotten comfortable with using Gmail and even Google Docs (for my recent wedding reception we used it to keep track of invites, etc.) so I figured it was time to start recording data to the cloud. I created a Google Spreadsheet Document to record her hours.

Days of the Week

I was able to record the date, start and end time, and calculate hours. The only thing I was having trouble with was the “day of the week” function. First of all that page I just linked to has a typo error, after “type = 1” it should read “starting from Sunday (Sunday = 1),” and I have left a comment stating as much. Anyway the function “weekday(date,type)” returns a number for the day of the week. So for the default type (type 1) if the date it gets is Jan 1, 2011, like so: weekday(1/1/11) the function will return “7” instead of “Saturday.”

This was really annoying, as my mom and her clients will not want the “days of the week” column to have numbers. Here is what it would look like with some sample dates:

dayofweek1

Instead of entering a date manually, column B is grabbing the date from column A, which is why you see in B2 the formula reads “weekday(A2),” where A2 holds the date value.

I found a solution via Google Search and although the code is short and sweet, I don’t really understand the function it uses very well and didn’t like the way it was printing the days of the week. The author explained how to change that text but it wasn’t working for me.

update: please check the comments section for (the 3rd comment), using SWITCH is much easier

The big IF/ELSE formula

So as a novice programmer it was obvious the simplest solution was to create a very nested if/else statement that would check the numerical value and substitute the appropriate day of the week. It’s not pretty but it gets the job done.

The IF statement in Google Spreadsheets works like this:
IF ( test , value of cell if TRUE , value of cell if FALSE)

So I would start with Sunday and have: IF (weekday(Date in Cell) = 1, “Sunday”, FALSE)

Except I don’t want to use “FALSE” in the false, I should begin the next statement because if it isn’t Sunday then maybe it’s Monday, so It should read:

IF (weekday(Date in Cell) = 1, “Sunday”, IF (weekday(Date in Cell) = 2, “Monday”, FALSE)).

And you can imagine this should continue for Tuesday, Wednesday all through Saturday, until the actual statement is this, where “Date in Cell” is replaced by A2:

=if(weekday(A2)=1,"Sunday",if(weekday(A2)=2,"Monday",if(weekday(A2)=3,"Tuesday",if(weekday(A2)=4,"Wednesday",if(weekday(A2)=5,"Thursday",if(weekday(A2)=6,"Friday",if(weekday(A2)=7,"Saturday","FALSE")))))))

And here is a picture of it working:

dayofweek2

Of course you can feel free to change the text within the parenthesis to fit whatever you need. If you want “Mon” you can just take out the “day” and it will only show “Mon” for that week. The point is you don’t need a number column, just use this formula and forget column B.

I hope this may be useful for someone out there.

update: please check the comments section for Jonathan’s solution (the 3rd comment), using SWITCH is much easier

Advertisements

14 thoughts on “Google Spreadsheets Day of the Week

  1. This should shorten things up:
    =CHOOSE(B1, "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday")

    I knew there had to be some kind of a Switch statement that you could use in a spreadsheet formula. Although this appears to be somewhat limited (only works for n=30 cases) it will work for you. 😉

  2. Jonathan, thanks! I am familiar with Switch statements but you’re absolutely right, I didn’t know Google Docs would have that. Now that is far, far more elegant and I have updated my spreadsheet to use that instead. Part of the problem is that I was still thinking in C++ syntax and didn’t see any SWITCH commands and figured it wasn’t in this program. I really should stop selling Google Spreadsheets short, I keep thinking it’s far inferior to the desktop versions (Office, Libre) in terms of formulas.

    I did have a question.. when I was writing my post I really wanted to have the code in a different font, ideally in a table, but I was having trouble using the correct formatting options. Did you just have a different font and a highlighted background for the code?

    • 1. Nope, I used the “code” HTML tag like this: <code>Your code here</code> . Sometimes in visual editors this is represented by a button with a hash “#” symbol, not sure about with Live Writer (also for future reference, they way I was able to print the HTML in this comment instead of having it be interpretted by the browser is to replace the HTML brackets with HTML character codes for “less than” and “greater than” signs.

      2. This is just FYI. If you ever want to reference a specific comment, you can link to it using the “Permalink” link found next to the person’s name. This is sometimes useful if you want to reference something in one post that was brought up in the comments of another post. Also, the syntax will change depending on what WordPress theme you’re using. Sometimes the comment permalink won’t be the text “Permalink” but will be the date/time of the comment, or the comment number, or something like that. Sometimes it can be hard to find the Permalink link.

      3. Also just FYI, WP comments are nested

      4. I’ll stop commenting so much now! Glad that one helped though. It’s the benefit of open-sourcing your code 🙂

      • Thanks again. I completely forgot about the code tag. Also it seems the Windows Live Writer doesn’t have an option for this but like most editors/coders for HTML files the bottom has three tabs: “Edit,” “Preview,” and “Source.” I can quickly jump to the source and begin coding in HTML.

        I actually had no idea how you incorporated html tags without it being read by the browser so glad you included that little bit. I’ve forgotten quite a bit of syntax, sadly.

        As for referencing the specific comment I just used the tag link, luckily this theme though has permalinks next to each comment (at least when I’m logged in).

  3. Pingback: Google Docs, Forms « usamaisawake

  4. My understanding is that the functions in Google Spreadsheet represented a subset of the functions available in OpenOffice Calc… The reason I thought that is because so many of the functions had the same name and syntax. I just went to double check, and noticed the “About this documentation” at the bottom of this page: http://docs.google.com/support/bin/answer.py?answer=82712

    When I was a freelancer, I used freshbooks.com to track hours and generate invoices. It was very flexible in allowing me the type/unit of work I wanted to track. It could email or snail mail the invoices to my clients. It had great features to track payments, send payment reminders, etc. It is free for a certain number f clients. I highly recommend it.

  5. found a much easier way to do this, you can use the formula
    =if(weekday(A1+1)=7,A1+3,A1+1)
    basically, if the day after A1 is a saturday, skip to the monday, otherwise use the next day
    A1 has to be manually entered as the starting date, then this formula goes into A2

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s