Forum Discussion
Referencing multiple time ranges on excel
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!!!
4 Replies
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.
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
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.
