Excel time and date

New Contributor
Hi folks,

I’m completely new around here and have actually been pointed this direction by Leila Gharani after asking a question on LinkedIn.

So here goes, I am trying to create a master file in which I can paste some employee timesheet details.

I have some tables at the top of the sheet that populate based on the data pasted below. The data pasted is always in the same format. The difficulty I’m having is trying to create an “overtime profile” table. In this table I wish to show how many hours have been claimed by an employee between certain days, and if how many hours they have worked on Saturdays and Sundays.

Is there a formula or VBA code that could be used? Is this kind of query even possible??

I really appreciate anyone taking their time to read this, and if you know of anything that could help I would be forever grateful.

Thanks for reading, take care,

3 Replies


Time Sheet

Report regular and overtime hours worked with this simple timesheet template. Perfect for small businesses, contractors, or the self-employed, this Excel timesheet template records time in, time out, and lunchbreaks for each day of the work week. As a timesheet in Excel, the total hours, regular hours, and overtime hours are automatically calculated. This timesheet template can also help you track the efficiency and productivity of teams within your company or business unit. This is an accessible template. Excel.


If you want something specific, then I recommend you to insert a specific file (without sensitive data). Explain your plan on the basis of this file.

Thus, you will be offered help in response to your needs.


Thank you for your patience and understanding.


If the template link from Microsoft helped you, Please Mark and Vote if it helps, as it will be beneficial to more Community members reading here.


I would be happy to know if I could help.



I know I don't know anything (Socrates)


Thank you very much for your reply and examples.

I have attached the file that you’ve made, I hope it explains what I am trying to do better?

The last bit I need to try and understand/make work is the Overtime Profile table. I would like it to calculate if an employee has claimed overtime based on the time/date columns. Any hours after 17:00-00:00 Monday to Friday are at x1.5 basic rate and 00:01-08:30 are at x2 basic rate.

I am trying to have this Overtime Profile table so that it makes payroll easier and takes the thinking out of totalling up hours.

Many thanks,
Sorry that should read, “I have attached the file that I’ve made”