• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculate # Hours in for Shift 1,2 & 3 contained during a worked time span

VChiz

VChiz

New Member
Hello Excel Guru's,

I need some help please. The situation is that I need to exctract a massive amount of start punch times and end punch times and then manually determine how many hours are due for each shift that time frame contains.

For example my shifts are:
1 = 0800-1500
2 = 1500-2300
3 = 2300-0800

If my start punch was 1230 and my end punch was 2030 it would expect to results as such:
Shift 1 = 2.5 hrs
Shift 2 = 5.5 hrs
Shift 3 = 0 hrs

Likewise if my start punch was 16:30 and my end punch was 06:01 my results should be:
Shift 1 = 0
Shift 2 = 6.5 hrs
Shift 3 = 7.02 hrs

I can pull a date column as well for the punch if it makes the task easier, I've attached a worksheet with a sample of what I'd like to create, I just need the forumlas for counting the amount of time for each shift created (pretty much everything).
 
Welcome to the forum!
It looks like the workbook you mentioned didn't get attached. Can you try again please?
 
Nice job, @John Jairo V . Always glad to share...leaves me more time to read my books, ha ha. Looks like the pink cells show duration in regular time format, and purple shows time in hours?
 
Thx @Luke M, you're right! The two colors are the same... just depends the format that we want to see the result time... just multiply by 24 and change the format to General (or fixed number, if you want). Blessings!
 
Thank you both! John bless you 10 fold :). This helps so much. You are both super speedy responders and I appreciate that. I tried to upload the file again and my system shut down... gotta love it.
 
UPDATE: The formula on the attached spreadsheet works most of the time but it doesn't work where there are hours that belong to the shift at the beginning AND end of the shift. See attached workbook for examples.
 

Attachments

Hi again!

Please, Check this file. I divided the Shift 3 in two Shifts (3A and 3B) for minimize formulation.

Please Comment! Blessings!
 

Attachments

WOW!!! John you are :awesome: (awesome!). I was so impressed with what you did before but, I'm blown away by this. It worked for everything I needed thank you so much!!! You should be one of the Excel Ninjas!!!!

Many Blessings to you.
 
You must log in or register to reply here.
Back
Top