Recently I wanted to make myself a spreadsheet to track earnings from customers. I had no idea what I wanted to do when I started but I set out to create one. One of the things I wanted was to auto populate week numbers and dates. Usually with Excel you can add a number into a cell and then drag copy this cell downwards and then it will increment to the end of your selection. Using Google Spreadsheets this did not happen. My work around for this was to do a simple formula. My running count goes in the direction of the numbers and not the letters.
In cell A3 I put the number 1This increments the number portion of the cell 'A3, A4, A5 ...' and keeps adding 1 to the previous running total which increments the number. In the end I did not care for the week number and decided to try the same with the date. Now with Google Spreadsheets I found that the date is in fact an integer, so you can do simple math with the date. In cell B3 you can type the date which I wanted the first Monday of the year which happened to be Jan 2nd 2012. This has the value of 40910 so to get the following Mondays all you have to do is
In cell A4 I put '=A3+1'
Drag this downwards
Put the correct date in your first cellNow the one thing I wanted to solve was to have a line through the current week so that I did not have to note the current date and then look for it between two other dates in my list of dates for the year. I quickly figured a condition in my head to deal with this which was as follows.
In the cell underneath type '=B3+7'
Drag that down as far as you need to (usually 51 times)
Get today's dateThe trouble is that I had never actually done ANDing or ORing with spreadsheet formulas. In other languages you may use IF (date => 1/2/2012 AND < 1/9/2012) then "-->" Just so you know this resembles no language I know so I am fully aware that this will not work but that is the jist I was going for.
Each cell will test the date in the cell to the right
See if today's date is equal or higher AND lower than the date in the cell below.
If true then display a --> to indicate this is our current week.
Else display nothing.
My solution was to use nested IF formulas and I'll explain.
=if(today()>=B3,if(today()<B4,"-->",""),"")
This tests to see if today's date is equal to or greater than the date in B3. The date in B3 is 1/2/2012 so as long as today's date is 1/2/2012 or greater then it is true. If I left this like it is then my spreadsheet would have arrows all the way down to the last week which would dissappear as the weeks went on. I did not want this to work this way so now instead of putting an arrow in the true section, I added another IF.
=if(today()>=B3,if(today()<B4,"-->",""),"")
This tests to see if today's date is less than the date in B4 a.k.a. B3+7. The date in B4 is 1/9/2012 so as long as today's date is 1/8/2012 or less then it is true. Combined with the previous condition we should now only get a true when today's date is equal or greater than 1/2/2012 AND less than 1/9/2012. All that remains is to finish off the two false conditions.
=if(today()>=B3,if(today()<B4,"-->",""),"")
The first NULL string is the false condition for 'today()<B4' which is all of the cells that are later in the year. If I had wanted to fill those cells with a string as in "NOT YET" which would imply future tense then I would do this here. The second NULL string is the false condition for 'today()>=B3' which is all of the cells that are earlier in the year. If I had wanted to fill those cells with a string as in "SEE YA" which would imply past tense then I would do this here; however I just wanted these to remain blank.
 |
| How I arranged the Spreadsheet |
The final step is to drag copy this formula down to the last date and this will copy the formula replacing all of the B3 and B4 with their increments and making a working spreadsheet which will put an arrow next to the correct week. I added some conditional formatting for presentation.
The link to the spreadsheet template which I have created can be found at this link if you would like to see the formulas and how they work
https://docs.google.com/spreadsheet/ccc?key=0Ami5WyujvXktdHZNanA3SnVPdmxZMEJjeHJ5dzMtWXc
If this guide informs you in any way please comment below. Thank you.
No comments:
Post a Comment