Assistance needed with Breaksheet

[kidamel]

Good morning everyone,

First time posting and I tried searching but unable to find what I'm looking for. If I missed it please direct me to the correct location.

I'm creating a breaksheet for my location. I'm having a hard time getting the cells to calculate from each other. Here's what I'm trying to do:
A1 = # of hours of the shift for example 8.5
B1 = the start time of the shift 5:45
C1 = Break #1 30
D1 = Break #2 30
E1 = B1+A1

It seems easy but here's the issue. B1 is formatted 0\:00 and I cannot get it to calculate based off of cell A1 which is a general number. Should I format A1 to represent the number of hours in minutes?

My next issue is, is there a way to have it determine how many breaks that row will allow? For example for C1 and D1 for it to enter in a 0 / 15 / 30 based off of the number of hours entered in cell A1.

Below is a grid we follow for breaks.
Hours Worked Lunch Breaks
0.0-6.25 0 15
6.50-8.25 30 15
8.50-10.25 30 30
10.50-13:25 30 15 30
13.50-14.75 30 30 30
15.00 + 30 30 30 30


Thank you in advance and any assistance would be amazing!!!

[slx]

Without formulas you should use minutes all the way through, and when you need to total work time then convert it.


Its easier to covert the total by subtracting start and stop, then use =hour() and =minute()/60

[kidamel]

Without formulas you should use minutes all the way through, and when you need to total work time then convert it.


Its easier to covert the total by subtracting start and stop, then use =hour() and =minute()/60

I'm open to formulas

[ben_hensel]

Welcome to the forum!

I tried searching but unable to find what I'm looking for.

This is why our rule #1 is "Clear, specific thread titles." Please updated your thread with a title that more accurately reflects your need: "assistance needed" describes 99%+ of threads around here, so it doesn't really tell anyone anything.

As for your issues:
1) Excel handles time as a serial code from 0.00 at midnight around the clock to something like 0.99 at 11:59 PM. (Since dates are integers from Jan 1, 1900, this is so a date/time value can be expressed as a single number, nnn.nnn or so).

So your problem, as such, is that the formula is going to asses "8.5" as something like eight and a half days, not as 8 and a half hours.

Please Login or Register  to view this content.

That will turn the 8.5 into 8h 30m and add it to the start time.

2) Break times is a simple INDEX(MATCH) (or VLOOKUP) off the table of information.

[kidamel]

Welcome to the forum!



This is why our rule #1 is "Clear, specific thread titles." Please updated your thread with a title that more accurately reflects your need: "assistance needed" describes 99%+ of threads around here, so it doesn't really tell anyone anything.

As for your issues:
1) Excel handles time as a serial code from 0.00 at midnight around the clock to something like 0.99 at 11:59 PM. (Since dates are integers from Jan 1, 1900, this is so a date/time value can be expressed as a single number, nnn.nnn or so).

So your problem, as such, is that the formula is going to asses "8.5" as something like eight and a half days, not as 8 and a half hours.

Please Login or Register  to view this content.

That will turn the 8.5 into 8h 30m and add it to the start time.

2) Break times is a simple INDEX(MATCH) (or VLOOKUP) off the table of information.

Awesome thank you! Oh I updated the thread name hope that's acceptable