• Megan Johanson

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.
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.
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.
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.
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.
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. The days with more votes are a darker blue.
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.