Convert PDF Schedule to Excel: Rosters and Timetables

To convert a PDF schedule to Excel in a usable form, get the grid in first, then repair it. Split ranges like "9:00–17:30" into real start and end times, unmerge the day cells and fill them down, and reshape the names-by-days grid into one row per shift so you can filter, total and sort it.

Why do schedule times turn into text after a PDF conversion?

Schedule times turn into text because a PDF cell often holds more than one time, or a time with extra marks. "9:00–17:30", "9am", "09.00" and "OFF" are all readable to a person and none of them is a single Excel time. Excel accepts a cell as a time only when it holds one value it recognises.

Excel stores a time as a fraction of a day: 12:00 is 0.5 and 18:00 is 0.75. That is what makes time arithmetic work, because subtracting a start from an end gives hours worked as a fraction you can format. A cell that holds text instead has no value to subtract.

Common forms you will meet in a converted roster:

• Ranges such as "09:00-17:30" or "9:00 – 17:30", with a hyphen or a longer dash.
• 12-hour times such as "9am" or "5.30pm", which Excel does not read without a space and a recognised separator.
• Dots as separators, as in "09.00", which Excel may take as the number 9.
• Codes such as "OFF", "AL" for annual leave or "S" for sick, which are not times at all.

Before fixing anything, decide which of these your schedule uses. The fixes differ, and running the wrong one on a whole column makes a mess.

Splitting shift ranges like 9:00–17:30 into start and end times

A shift range needs to become two cells, a start time and an end time. Split on the dash between them, then make sure each half is a real time. After that, a third column with end minus start gives the hours for every shift.

The steps in Excel:

1. Normalise the dash. PDFs often use an en dash (–) rather than a hyphen (-). Use Find and Replace to change every "–" to "-". Also replace " - " with "-" so spaces do not get in the way.
2. Split the column. Select it, choose Data, Text to Columns, Delimited, and enter "-" as the Other delimiter. Put the result in two empty columns.
3. Check the type. A real time aligns right. If the halves are still text, select them and use Find and Replace to replace ":" with ":". Excel re-enters each cell, and that usually turns it into a time.
4. Format as time. Press Ctrl+1 and choose a time format such as 13:30.
5. Add hours. In a new column, use =(End-Start)*24 with your cell references, and format it as a number. A 9:00 to 17:30 shift gives 8.5.

If dots were used, as in "09.00-17.30", replace "." with ":" before step 2. Do that only in the time columns, or you will damage any decimal numbers elsewhere.

Leave codes that sit among the times

Codes such as "OFF", "AL" or "S" will not split into times, and they should not be thrown away either, because they record who was absent and why. Before splitting, copy the shift column into a new Code column. Then clear the times out of Code and clear the codes out of the shift column, so each column holds one kind of value. A filter on Code later answers questions like how many leave days were taken in a month, which a column of failed times never could.

Merged day cells in a converted PDF timetable

A timetable often prints a day once, in a tall cell that spans several rows of shifts or classes. After conversion that becomes a merged cell, or a value in the first row with blanks beneath. Either way, only the first row knows its day. Unmerge, then fill the day into every blank below.

In Excel:

1. Select the day column and choose Merge & Center to turn merging off. The day stays in the top cell only.
2. With the column still selected, press F5, choose Special, then Blanks. Excel selects every empty cell in the column.
3. Type = and press the Up arrow, then press Ctrl+Enter. Each blank now copies the cell above it.
4. Copy the column and paste it as values, so the formulas do not change if you sort.

Check the bottom of each day after the fill. If the last row of Monday was blank in the PDF for a reason, such as a note row, it has now been labelled Monday too. Delete such rows before you fill.

Some timetables merge across columns as well, with a single cell reading "Staff training" over three days. Unmerging leaves the text in the first column only, so copy it into the other two by hand.

Reshaping a roster grid into one row per shift

A roster grid, with names down the side and days across the top, is easy to read but hard to analyse. Turning it into a list with one row per person per day lets you filter by name, total hours by week and sort by start time. Power Query's unpivot command does this in a few clicks.

Microsoft describes unpivoting columns in Power Query as unpacking attribute-value pairs into a flatter layout. Here the days are the attributes and the shift times are the values.

A worked example

The roster below is invented. As it arrives from the PDF, the grid looks like this:

The steps:

1. Select the grid and choose Data, From Table/Range. Confirm the table has headers.
2. In the Power Query editor, select the Name column.
3. Right-click it and choose Unpivot Other Columns. Microsoft's page notes that this unpivots the columns you did not select, so a Thu column added next week will be unpivoted too on refresh.
4. Rename the new Attribute column to Day and the Value column to Shift.
5. Filter out rows where Shift is OFF.
6. Split Shift by the "-" delimiter into Start and End, and set both to the Time type.
7. Close and Load.

The result has one row per shift:

Chloe's rows look odd, with an end before the start. That is the next section.

If this list needs to join last month's, adding PDF data to an existing Excel sheet explains how to append it without breaking formulas.

Overnight shifts and 12-hour times in a converted roster

An overnight shift such as 22:00 to 06:00 ends the next day, so a plain end-minus-start gives a negative number. Add one day when the end is earlier than the start. Times like "9am" need converting to 9:00 first, because Excel reads them as text.

For overnight hours, the formula =MOD(End-Start,1)*24 handles both normal and overnight shifts. MOD wraps a negative fraction back into a positive part of a day, so 06:00 minus 22:00 becomes 8 hours. Use it on every row, not just the overnight ones; it gives the same answer for a day shift.

For 12-hour times, Excel can read "9:00 AM" with a space and the letters AM or PM. A converted "9am" lacks both. Two Find and Replace passes usually fix a column: replace "am" with " AM" and "pm" with " PM". Then add ":00" where a time has no minutes, which a formula can do for you.

Watch also for a schedule that writes midnight as "24:00". Excel reads that as the start of the next day, which is fine for hours but shows as 00:00. Note it in the sheet so nobody thinks a shift was lost.

Is a weekly PDF schedule worth converting every time?

A schedule that changes every week is worth converting only if you can reuse the cleanup. A Power Query that unpivots and splits the grid can be refreshed when next week's file arrives in the same layout. Without that, ask whoever makes the roster for the spreadsheet behind the PDF; it almost always exists.

Some PDF readers can export straight to a spreadsheet, which saves one step each week. Where the save-as-Excel export hides in PDF readers shows which readers offer it. If the roster reaches you as a photo pinned to a noticeboard, not a PDF, converting an image to Excel online covers that case.

On a computer without Microsoft Office, LibreOffice Calc can do most of this work, though it has no Power Query. Converting PDF to Excel on Linux describes the LibreOffice route.

Rosters are not what FastBankConvert is for; it is being built to convert bank statement PDFs, and it is getting ready to open. Its homepage explains the plan.