Can someone help me build an excel template query?

Question

Can someone help me build an excel template query?

ASeal 0

Hi. I need help making a schedule template in excel based off a template from Shifts for Teams. I made a post before here: https://answers.microsoft.com/en-us/msoffice/forum/all/can-i-duplicate-spreadsheets-with-formulas/dc6b0209-f04a-40ee-830a-96b6829853d5. Volunteer Andreas Killer built the template before, and I need help again. Can someone help? Basically, I need the same thing, except the query needs to read the time input as [8:00 am - 4:30 pm] instead of [8:00]. And there a no unpaid breaks on Sundays.

ASeal 0 Reputation points

2025-08-13T02:43:54.83+00:00

Here is the file I'm working with.

https://1drv.ms/x/c/d721478a84211708/EYl9r5vEYfxBgWI0hL7RxpEB5t46WMlw4P01CRC78jXKcA?e=jCfErB

I need the data from the "Template" tab to populate in the "Shifts" tab.
Andreas Killer 144K Reputation points Volunteer Moderator

2025-08-13T07:06:24.16+00:00

Hi,

This is possible, but the layout of your template sheet is awful.

You have two merged cells per day, and you write the from-to times as text in a merged cell. Honestly, you can make life difficult for yourself.

Split the cells, the from and to times go into separate cells as the correct time. Then the calculation below becomes very easy.

C5: =D4-C4+IF(D4<C4,1,0)

Format as duration.

If you're done with that 2 rows:

B4: =SUM(C5:P5)*24

AE4: =SUM(Q5:AD5)*24

Your template looks the same as before, but now we can use it for analyze. Next step is to create a locale named range "DataA" as in my other sample file A3:AC35

Load this into Power Query and remove Column2, it's superfluous.

Transpose the data to get the dates as Column1 and you see that you have a date and null in the next row. Add a custom column "From" if there is a date otherwise "To". Merge this column with Column1 and e.g. use a semicolon as delimiter.

Move the column as first column, transpose again and use first row as headers.

This is the first preparation.

Now filter out all the null in the employee column to get rid of all the durations.

Right-click the employee column and unpivot other columns.

Now split the Attribuet column by ; to separate the date and From/To

Pivot the FromTo column and use the Value column (the times) as value column.

That's it. Now you have the data you need.

In my sample file I made the function fnTransformData from these steps, because we need the same for DataB A38:AD70

Anything else is the same as in my sample file.

I can re-design your file and do all the work, but not for free.

Andreas.
ASeal 0 Reputation points

2025-08-14T03:07:37.92+00:00

I would love to learn how to do this on my own, but I don't have enough background knowledge to follow along. I'm already stuck at "Locale named range "DataA" as in my other sample file A3:AC35." Can you elaborate? If all else fails, how much?
Andreas Killer 144K Reputation points Volunteer Moderator

2025-08-14T14:03:04.7233333+00:00

It is not permitted to make offers or discuss this here. Everything I show here is generally free. I'm happy to help, but please understand that I can't spare a few hours of my free time for you.

If you want to load data into Power Query, the usual way is to format the data as table. but it is also possible to load the data from a named range. Open my sample file and select DataA:

If such a range is selected and you click "Data \ From Table/Range" in the ribbon, Power Query loads the data from that range instead of creating a table.

I believe the instructions above are sufficient for any experienced Excel user to follow them, especially since there is a sample file.

If you can't find someone to make the changes for you, please contact me at andreas(dot)killer(at)gmx(dot)net and please use the subject of this thread and a link to this thread in the email body.

Andreas.
ASeal 0 Reputation points

2025-08-19T02:00:08.1633333+00:00

I've gotten this far:

How do I tell it the Start time and End time in the first image and have it reflect in the second image. I think once I get that part, all I'll have left is to get all the information into the Shifts template.

1 answer

Your answer

ASeal 0 Reputation points

2025-08-13T02:43:54.83+00:00

Here is the file I'm working with.

https://1drv.ms/x/c/d721478a84211708/EYl9r5vEYfxBgWI0hL7RxpEB5t46WMlw4P01CRC78jXKcA?e=jCfErB

I need the data from the "Template" tab to populate in the "Shifts" tab.
Andreas Killer 144K Reputation points Volunteer Moderator

2025-08-13T07:06:24.16+00:00

Hi,

This is possible, but the layout of your template sheet is awful.

You have two merged cells per day, and you write the from-to times as text in a merged cell. Honestly, you can make life difficult for yourself.

Split the cells, the from and to times go into separate cells as the correct time. Then the calculation below becomes very easy.

C5: =D4-C4+IF(D4<C4,1,0)

Format as duration.

If you're done with that 2 rows:

B4: =SUM(C5:P5)*24

AE4: =SUM(Q5:AD5)*24

Your template looks the same as before, but now we can use it for analyze. Next step is to create a locale named range "DataA" as in my other sample file A3:AC35

Load this into Power Query and remove Column2, it's superfluous.

Transpose the data to get the dates as Column1 and you see that you have a date and null in the next row. Add a custom column "From" if there is a date otherwise "To". Merge this column with Column1 and e.g. use a semicolon as delimiter.

Move the column as first column, transpose again and use first row as headers.

This is the first preparation.

Now filter out all the null in the employee column to get rid of all the durations.

Right-click the employee column and unpivot other columns.

Now split the Attribuet column by ; to separate the date and From/To

Pivot the FromTo column and use the Value column (the times) as value column.

That's it. Now you have the data you need.

In my sample file I made the function fnTransformData from these steps, because we need the same for DataB A38:AD70

Anything else is the same as in my sample file.

I can re-design your file and do all the work, but not for free.

Andreas.
ASeal 0 Reputation points

2025-08-14T03:07:37.92+00:00

I would love to learn how to do this on my own, but I don't have enough background knowledge to follow along. I'm already stuck at "Locale named range "DataA" as in my other sample file A3:AC35." Can you elaborate? If all else fails, how much?
Andreas Killer 144K Reputation points Volunteer Moderator

2025-08-14T14:03:04.7233333+00:00

It is not permitted to make offers or discuss this here. Everything I show here is generally free. I'm happy to help, but please understand that I can't spare a few hours of my free time for you.

If you want to load data into Power Query, the usual way is to format the data as table. but it is also possible to load the data from a named range. Open my sample file and select DataA:

If such a range is selected and you click "Data \ From Table/Range" in the ribbon, Power Query loads the data from that range instead of creating a table.

I believe the instructions above are sufficient for any experienced Excel user to follow them, especially since there is a sample file.

If you can't find someone to make the changes for you, please contact me at andreas(dot)killer(at)gmx(dot)net and please use the subject of this thread and a link to this thread in the email body.

Andreas.
ASeal 0 Reputation points

2025-08-19T02:00:08.1633333+00:00

I've gotten this far:

How do I tell it the Start time and End time in the first image and have it reflect in the second image. I think once I get that part, all I'll have left is to get all the information into the Shifts template.

Answer 1

Dear ASeal

Welcome to Microsoft Q&A Forum. Thank you for reaching out and posting your question.

Based on the 'macOS' tag included in your post, I have created and tested the following steps on Excel for Mac to ensure it meets your requirements:

Step 1: Setting up your data as an Excel Table is the key to making this template automatic, as it will copy formulas down for new entries

In a new sheet, set up your headers in the first row: Name, Date, and Shift.
Enter a few rows of sample data to test with, including a weekday, a Sunday, and a day that is "Off" or blank.
Select your entire data range, including the headers.
press Cmd + T to format it as a Table > Tick the "My table has headers" checkbox > Click OK.

Step 2: You need to add the three calculation columns, and enter formulas in the first data cell of each column

*Column 1: Total Hours will calculate the shift duration and return 0 if the cell is blank or contains non-time text like "Off". *

Formula:

=IFERROR(LET( start_time, TIMEVALUE(TRIM(TEXTBEFORE([@Shift]," - "))), end_time, TIMEVALUE(TRIM(TEXTAFTER([@Shift]," - "))), duration, (end_time - start_time + (end_time < start_time)) * 24, duration ), 0)

Column 2: Unpaid Break will correctly apply the 0.5-hour break only when hours have been worked and the day is not a Sunday.

Formula:

=IF(AND([@[Total Hours]]>0, WEEKDAY([@Date], 1)<>1), 0.5, 0)

Column 3: Payable Hours will subtract the break from the total hours to get the final payable amount.

Formula:

=[@[Total Hours]]-[@[Unpaid Break]]

Your template is now complete and will automatically calculate all hours correctly based on your input. The final result is shown in this image: User's image

Additionally, to add a new entry, simply start typing in the row directly below the last line of your table. The table will expand, and all the formulas will be automatically filled in for you. If you only want to display the final Payable Hours, you can right-click on the Total Hours and Unpaid Break columns and select Hide from the context menu.

I hope this step by step guide will help with your template. If you need the template file with my suggestions, please feel free to contact me via private message.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". User's image Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Flora-T 7,855 Reputation points Microsoft External Staff Moderator

2025-08-14T07:39:49.8533333+00:00

Hi ASeal

I hope you're doing well.

I truly appreciate your efforts and continuing to engage in this discussion, it's truly inspiring to see your determination to learn and solve this on your own.

You can take advantage of the suggestions above and consider using Power Query to combine and output your data. Power Query can automatically transform that data to populate your "Shifts" tab, no manual copying or pasting needed.

As a forum moderator, my goal is to support you as best I can by guiding discussions, explaining Excel features, and sharing community knowledge so you can build a solution that works for you. To keep the learning spirit of the forum, I can’t build or edit files directly. This is partly due to the lack of more back-and-forth and context about your workflow, and also because of privacy.

I’m here to share targeted advice and help bridge any gaps based on Microsoft official documentation and community insights.

Once again, thank you for reaching out to this forum with inquisitive mindset. Hope the details provided will assist you effectively.
ASeal 0 Reputation points

2025-08-17T18:46:24.15+00:00

I've gotten this far:

How do I tell it the Start time and End time in the first image and have it reflect in the second image. I think once I get that part, all I'll have left is to get all the information into the Shifts template.

Share via

Can someone help me build an excel template query?

1 answer

Your answer