Visualizing 81 Delivery Date Predictions

Is due date an accurate prediction of when a baby will be born? Can my family, friends, and coworkers do a better job at predicting when baby will arrive?

Predictions

My due date is May 1st and I couldn’t miss the opportunity to poll my friends, family, and coworkers on their predictions, as well as try out a new-to-me type of visualization.

I solicited birth date predictions from my family, friends, and coworkers over the past few months and turned the data into a heat map calendar. Some people made guesses based on my mom’s delivery timelines, some based on how I was carrying the baby, and some based on the closest new moon to my due date.

Follow along as I transform the predictions into a visualization and then stay tuned to see if their most frequent guess was right!

Setting Up the Excel Calendars

First, I set up three calendar tables in Excel for the few weeks before and after my due date. I wanted to know whether predictions differed between family/friends and coworkers, so one table was for family and friends, one was for coworkers, and one was for everyone combined.

Calendar table with dates for a few weeks before and after my due date.

Setting Up the Calendar of Votes

Next, I copied each calendar to the right of the original one and deleted the dates. I entered the numbers for how many votes each date got into the relevant cell. So, for my family and friends calendar, it looked like this:

Table showing the number of votes for each day surrounding my due date.

For the combined calendar, I created a formula that would add together the votes from each of the two calendars for each day. There were a total of 81 guesses.

Formatting the Calendar for Visual Insights

Then, I applied conditional formatting for the new tables, where the cells with the higher numbers/votes were filled with a darker blue.

Table of votes conditionally formatted with darker cells indicating more votes.

Finally, I made a few formatting tweaks:

1. I reduced the width of the columns and made them uniform by going to Home – Cells – Format, and choosing Column Width. There I set the width to 6.

2. I hid the vote counts in the conditionally formatted tables by highlighting each table of votes, right clicking, and selecting Format Cells. Under the Number tab I selected Custom and deleted what was in the Type: box (see image below). Instead I entered three semicolons “;;;” which serves to hide the numbers while keeping the formatting visible. Then I clicked OK. If you click in a cell you can still see the number in the formula bar.

Formatting the data cells to hide the number of votes.

After this formatting, the table looked like this:

Conditionally formatted table of votes with no visible numbers.

Layering the Calendar Dates and Votes

Here is where I ran into a challenge. I wanted the vote counts to be represented by the color of the cell, but I also wanted the date to appear in the cell. After some googling I didn’t see a straightforward way to do that in Excel without complex formulas or coding, so I did a simple workaround.

I made sure the columns for the original calendars with dates were the same width as the colored calendars and then carefully copied and pasted into PowerPoint *Keeping Source Formatting* first the colored calendar and then the calendar with dates.

I moved the calendar with dates over top of the colored calendar until the dates were aligned under the correct days of the week. I also added a title and expanded the subtitles to create a standalone visualization. To group everything together I selected all the tables and copied them, then I pasted them as a picture.

Final Predictions

The end result looks like this:

Final visualization showing birth date predictions for each day.

As you can see, May 3rd is the favored day with 9 votes, followed by May 5th with 8 votes. There were 6 votes for my actual due date, May 1st, which accounts for 7% of the guesses. My coworkers were more likely than my family and friends to choose a day within one week of my due date.

Only time will tell how accurate the predictions are! Stay tuned for an update.