A short one, but I found myself wasting time recently. I use an Excel sheet to schedule some items out each day. It’s not my choice to do this, but the service I use requires this to not manually set time for each event.
Tl;dr: Add minutes/1440 to the date.
The format looks like this:
I’ve been changing the dates each day to the next day, which involves picking the cell, hitting F2 (or clicking the mouse), moving to the day and editing it to increment. In this case, I’d go to the 10th for the next set of scheduled items.
Manual, time consuming, and when I saw this cartoon, I knew I needed to do something else. It’s a few minutes of my day, and really, it’s 12 hours over 5 years.
But it’s incredibly annoying and tedious. It makes my blood pressure rise, and I delay the work because of it.
I spent 5 minutes considering a few things. I knew I could do any of these, but some would require more work than others, and perhaps not be worth the time.
- Use a macro to change each entry
- Get my source system to generate an Excel sheet in this format
- Write OLE automation to generate a new sheet
In the end, the simple solution was just learn to add time to a base date. If I take the first entry, I can see that I’m really adding minutes (or hours) to this one. So I decided to do that. In this way, I still need to edit the first cell, but then all the rest will work.
However I needed to add minutes to an Excel entry. I wasn’t sure how, but a quick Google search gave me the answer from Superuser. Because of how Excel stores dates, I really need to add minutes divided by 1440 to the date.
So my first date is: 02/09/16 14:05. To get my next entry, which is 02/09/16 14:22, I need to add 17 minutes, but the forumla looks like this:
As you can see, that’s the right time. I now had to spend about 10 minutes setting the formula for each time, which is a matter of calculating the minutes for each new time. It’s not too hard, and my ten minutes spent here won’t pay off in time for a long time, especially after this blog post, but it will reduce my stress quite a bit.
Every day.
Filed under: Blog Tagged: Excel, syndicated, work 
