Referencing multiple time ranges on excel

[richardskelly]

Hi,

I'm STRUGGLING!

I am creating an excel sheet for counting up hours worked over a two week period, it includes a few different types of hours (sick leave and annual leave etc.) I have gotten so far and I cant go any further.

Where some one has worked from 09:00 to 17:00 I need to get this to populate a box at the end of the row for that day that looks like this - 09:00-17:00. 

Also

If some one has worked on site and off site at different times on the same day I need to represent this exactly. so it will look something like - 09:00-17:00 (on site) 17:00-21:00 (off site) 21:00-23:00 (on site).

I have created a mock up of what i need in a line below the field and attached that below,

Can someone please help me with this as I am at the extent of my Excel capabilities.

Thank you so much in advance!!!

[#2]

Hi

First you need a list-based table and not a cross tabular report.

Second because your smallest time granularity is 30 minutes your input must be always in 30 minutes intervals.

See my Power Query solution in attached file.

[#3]

Hi,

Thank you so much for your time, I really appreciate it!

I am doing this to help the salaries department in my hospital who are currently doing this manually on a piece of paper. I am one of the employees here and have nothing to do with salaries but thought it would be great to digitize the overtime sheet.

In order for them to take it I need to make sure it looks the smae as what they have now or they wont accept it, Its a nightmare!

What I have created is an exact copy apart from the columns to the right which I have added.

Is there any way of making it work within the constraints of the workbook I have sent originally?

Again, I am so grateful for all your help with this!

Thank you

[#4]

I created the output to be like the original.

Only the input is changed.

May be someone else has a solution that fits better with the original.

[#5]

Thank you so much for all your help I really do appreciate it!

Richard