Splitting date and time in Excel | bsport

Several Reports give you a single column that holds the date and the time together, such as Booked At, which looks like 2026-09-30 13:00:00. If you are used to a report that had two separate columns, one for the date and one for the time, this page shows you how to get back to that in Excel.

It helps you answer questions like:

How do I split one date-and-time column into two?
Why does my time column show something like 0.541666 instead of 13:00?
How do I look at just the hour of the day?
How do I find every record between 6pm and 8pm, on any date?

Splitting the column 📅

Download the Report as Excel, then follow these three steps.

1. Make room. Say Booked At is in column B. Insert two empty columns after it and label them Date and Time. In this example they become C and D.

2. Add the formulas. With your header in row 1 and the first record in row 2:

In C2:

=INT(--$B2)

In D2:

=MOD(--$B2,1)

Select both cells and drag the small square in the bottom-right corner down to the last row of your data.

3. Format the results. Select column C, right-click, Format Cells, and choose Date. Do the same for column D and choose Time, or use a custom format of hh:mm.

That third step is not optional. Until you do it, your Date column shows a five-digit number and your Time column shows something like 0.5416666.

With 2026-09-30 13:00:00 in B2, you end up with:

Booked At	Date	Time
2026-09-30 13:00:00	30/09/2026	13:00

Filtering and grouping by the hour 🕐

Once you have a Time column, pulling out the hour is one more formula. In E2, alongside the others:

=HOUR($D2)

This returns a plain whole number: 13 for one in the afternoon, 18 for six in the evening. Leave it formatted as General, not as a time.

That column is what you filter and group on:

To see one slot, click the filter arrow on the Hour column header and tick the hours you want. Ticking 18 and 19 gives you everything between 6pm and 8pm, across every date in your file.
To count by hour, select your data and insert a PivotTable. Put Hour in Rows and any column you like in Values, set to Count. You get one row per hour of the day with the total beside it, which is how you find your busy periods.
To compare a specific day of the week, add a second column with =WEEKDAY($C2,2), which gives you 1 for Monday through to 7 for Sunday. Filter on both columns together to answer something like "how busy are Tuesday evenings".

A quicker one-off method

If you just need the split once and do not want formulas, copy the Booked At column into two spare columns, then for each one use Data → Text to Columns → Delimited → tick Space. Keep the date part in one column and the time part in the other, and delete what you do not need.

💡 This is faster, but it is a one-time edit. If you download the Report again next month you will have to redo it by hand, whereas the formulas above just recalculate. Use Text to Columns for a quick look, and formulas for anything you plan to repeat.

💡If your Excel is set to a language that uses the semicolon as its argument separator, such as French, replace the commas in the formulas above: =MOD(--$B2;1) and =HOUR($D2). Excel will tell you the formula is invalid if you have the wrong one, so try the other if it rejects what you type.