How to Add Auto-Advancing Dates to a Spreadsheet Created With Google Sheets
(And how to create a basic mileage and related expenses spreadsheet for that matter.)
I needed to find an expense report template that could calculate mileage based on the current federal rate in a bi-weekly format. I also wanted it to automatically populate the dates in the daily fields based on the report start date. I couldn’t find one. So I made one.
Advancing the Date in a Google Docs Spreadsheet
I spent a little time looking through forums and help sites to find a way to automatically advance a date based on a starting date within a field. I couldn’t really find anything that told you how to do it so I guessed. I tried having the first line of the report populate the date from the report start date and then manually advancing the date by one day on the following line. I then hit the “+” on the bottom right corner of the cell and dragged the formula down to the last line of date entry. Google Docs did not get what I was trying to do. So I tried a simple formula. I took the cell that the report starting date was in, “B2,” and wrote the formula “=B2+1” in the first cell that I wanted the date to advance. Low and behold it worked! So for the next date I wrote “B2+2.” The date advance by two. So I did a +3, +4, +5, +6…. and so on till all the dates advanced the way I wanted it to. I used the date populated for the final entry date to populate the period ending date as well. This example is presented in the template link at the bottom of the page.
I was also able to advance the date another way as well by modifying how I calculated it. This is a slightly easier method best explained by screen caps.
Step 1: I populated the first daily date field with “=B2+0.” You could also populate it with just “=b2.” B2 is the cell location of the starting date of the report in the top line of the spreadsheet.
Step 2: I used the formula “=A4+1” to populate the next daily date entry.
Step 3: I then used the corner drag method to auto populate the rest of the daily date fields. I grabbed the corner with the mouse (Left Clicked”). Once the cursor turned into a large plus sign I dragged the formula down the length of the daily date entry fields and the proper formula series was replicated to properly advance the date.
So there you have two methods to advance a date in Google Sheets.
The Rest of the Math
If you have come across this article then you more than likely know how to make it add up the miles, tolls and how the multiplication formulas work. You can click on each total and look at the formula used in the formula entry area and easily figure it out if you don’t. This spreadsheet is for basic interoffice use and the colors won’t be seen by the staff since I am printing it to a black and white printer. You are free to change and modify it as you see fit. You can find the link to the Google Docs template below:
Final Note To Help You Save The Date
To change the start date of the report you don’t need to manually enter the date. Just double click on it and it will give you a calendar that you can use to select the date. When you change the date all the other dates affected by that cell will change accordingly.
Statistics recorded with Counterize - Version 3.1.4