Excel Formula for Scheduling Social Updates by Day of the Week

One of the clients that we work with has a fairly consistent seasonality to their business. Because of this, we like to schedule social media updates far ahead of time so that they need not worry about hitting those specific dates and times.

Most social media publishing platforms offer a bulk upload capability to schedule out your social media calendar. Since Agorapulse is a sponsor of Martech Zone, I’ll walk you through their process. As an observation, they also offer a bit of flexibility when uploading your comma separated value (CSV) file because you can actually map the columns of your file rather than having them hard-coded.

When we build the CSV file, we don’t want to just slam a tweet 7 days a week at the exact same time. We want to set the CSV to specific weekdays and some random times each morning. In this example, I’m going to fill the spreadsheet with social media updates for mornings on Monday, Wednesday, and Fridays.

Excel Formulas for Calculating Day of Week

Be sure to start with an Excel Spreadsheet, not a CSV file, since we’re going to use Excel Formulas and then export the file to a CSV format. My columns are pretty simple: Date, Text, and URL. In cell A2, my formula is to find the first Monday after today. I’m also going to set the time to 8 am.

=TODAY()+7-WEEKDAY(TODAY()+7-2)+TIME(8,0,0)

This formula jumps to next week and then finds Monday in the week. In cell A3, I just need to add 2 days to the Date in A2 to get the date for Wednesday:

=A2+2

Now, in cell A4, I’m going to add 4 days so that I get the date for Friday:

=A2+4

We’re not done yet. In Excel, we can automatically drag a series of cells for Excel to automatically calculate formulas in later rows. Our next 3 rows are just going to add a week to our calculated fields above. A5, A6, A7, A8, A9, and A10 are respectively:

=A2+7
=A3+7
=A4+7
=A5+7
=A6+7
=A7+7

Now, you can just drag the formula for as many updates as you’d like to import.

Random Times in Excel

Now that we’ve got all of our dates set, we may not want to publish at the exact time. So, I’m going to insert a column next to column A and then in column B, I’m going to add a random number of house and minutes to the time in column A, but not go past noon:

=A2+TIME(RANDBETWEEN(0,3),RANDBETWEEN(0,59),0)

Now just drag the formula down from B2:

There we go! Now we’ve got a column of Monday, Wednesday, and Friday days with random times between 8 am and noon. Be sure to save your Excel Spreadsheet (AS Excel) now. We may want to come back to this spreadsheet each quarter or each year as we schedule the next social updates.

Copying Values in Excel

Select Edit > Copy from your Excel Menu and open a new Excel Worksheet – this will be the worksheet we export to CSV. Don’t paste the column yet, though. If you do, the formulas will be pasted and not the actual values. In the new worksheet, Select Edit > Paste Special:

This provides a dialog window where you can select values:

Did it paste a number with a decimal? No worries – you just have to format the column as a date and time.

And now you’ve got the data you need! You can now populate the social updates and even add links. Navigate to File > Save As and select Comma Separated Values (.csv) as your File Format. That will be the bulk upload file that you can import into your social media publishing system.

If you’re using Agorapulse, you can now use their Bulk Upload feature to upload and schedule your social updates

How to Bulk Upload Social Updates in Agorapulse

Disclosure: I’m am an Agorapulse Ambassador.