Excel: repeat date range across multiple rows in chornological order

Question

Hi,

i hope I can explain this ok.

I am creating a room booking spreadsheet for office users with minimal excel skills.

More indepth details are on individual tabs with summaries on the main tab.

I am displaying the times in 15minute slots allowing for room booking in and out and looking to repeat the date next to it in chronological order.

Given the amount of reoccuring dates, I cannot use the drag fill, so wondering if there is another way to use a formula or function to have the dates repeating without me having to fill every cell manually. Looking to have the times repeat as well alongside the dates.  the date range I am looking for is going into the 2021 financial year.

I hope this makes sense. Thanks!!

Answer 1

Hi

It is difficult to visualize your requirement with the current information.

We don't have the info from the other sheets were we suppose to get the results from.

Reproduce your workbook on our side is very difficult

So,  I'd request you to 

Prepare and upload a sample/dummy file with no confidential/sensitive data

to Onedrive, DropBox... and share the link here

It will help us to give a prompt and right solution. 

Do let me know if you require any further help with this. I will be glad to help you.

Regards

Jeovany

Answer 2

I hope this makes sense. Thanks!!

Actually, you can use autofill to drag the date/times down as required.  Rather than using separate date/time columns, use a single date-time column. With a few examples it will drag appropriately.

If you need a year or more of data generated, you can use PowerQuery to generate a "Date Table" for you

2019 05 11   Creating a simple date table in DAX    2019 05 11

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

This article shows how to build a basic date table using a calculated table and DAX.

A date table is required for most time intelligence calculations such as year-to-date, previous year or moving averages. If a data model does not already have a date table, it is possible to create one using a calculated table and some basic DAX code.

The date table needs to follow a few rules:

    All the dates – from the first to the last day of each year – need to be present.

    Each date gets its own row.

    There are no holes allowed, even if a date is not referenced by an event.

    The table needs to include one DateTime type column.

.

2011 12 01   Using Query Editor to Create a Time Table   2011 12 01

https://PowerPivotpro.com/2011/12/using-query-editor-to-create-a-time-table/

The first thing I ever do when I create a new PowerPivot workbook is create the date tables that I need.  You can now create your own – from PowerPivot! In this post, there’s some SQL scripts that create time tables and you can simply cut and paste them into the query editor and use them straight away.

AND there’s one more bonus, especially for Accountants, you can set this up for YOUR Fiscal Year!

.

2011 11 15   The Ultimate Date Table 

I get a lot of questions from people who are struggling with the time intelligence functions in DAX.  And nine times out of ten, the answer is that they don’t have a proper date table.  I know it’s tempting.  You’ve got your sales table, and hey, there’s a Date column in there!  So you use it, and pass that column as a parameter to, say, DATESBETWEEN, or DATEADD.  Sometimes that will give you an error.  And other times, it won’t…  but the results will be funky.  You need a separate Dates table, or perhaps you prefer to call it a Calendar table.

.

2014 02 27   The Ultimate Date Table–Revisited      by Dominik Petri, Today he shares a cool “hybrid” technique that blends two of my favorites things – Data Market and Power Query! As you might already know from Rob’s “The Ultimate Date Table” post, you need a separate calendar table to unleash the power of PowerPivot’s time intelligence functions. You want one for free? Fully customizable? Running up to a variable end date? Updated automatically every time you open your workbook? The time has come…

Get a great calendar table – for free

.

2010 05 01   Calling a Stored procedure in PowerPivot    2010 06 00https://PowerPivotpro.com/2010/06/calling-a-stored-procedure-in-PowerPivot/

While building a new PowerPivot workbook i wanted to use Time Intelligent functions. One of thegolden rules of PowerPivot time intelligent functionsis to create a seperate related time table. I have created a stored procedure which creates a new table in PowerPivot. You can use this code to create the range.

.

Answer 3

Hello Squikid

I am V. Arya, Independent Advisor, to work with you on this issue. At what time, 25-May-20 would switch over to 26-May. Will it be at 12:00 AM or what is the closing time for a day?

Also is 8:00 AM start time of a day?

Answer 4

Hi Jeovany,

Please let me know if this link works 

https://1drv.ms/x/s!AvZVe8YeNqvaixyuvdfFcpP91zql?e=gvbGwB

Ive updated it a bit since the screen shot.

The individaul worksheets dont have much else really just who booked the room and later may have more info but the main tab is to show the prominant summary.

Thanks!

Answer 5

Hello!!

The date should switch over at 5:45.

Office hours are actually 8.30-5pm but have allowed for a bit of time either side to advise of the possible early/late meetings.