Calculating Time

[Afaes]

Hi All, can you please help me with the following question:

I can’t figure out how to calculate the time in Column A to get 8hrs in Column B. Is there a formula I can use in Column B that will give me 8hrs? Thanks!


Column A Column B
7am - 3pm ??

 

[Afaes]

Hello- below is an example. How would I write the formula to get the total hours worked for the week for Employee 1 and Employee 2?

Hours worked should say 40 for each employee.

Thank you

	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Hours Worked
Employee 1	7a-3p	7a-3p	7a-3p		7a-3p	7a-3p		?​
Employee 2		130p-930p		130p-930p	130p-930p	130p-930p	130p-930p	?​

 

[Peter_SSs]

Thanks for the sample data.
With 7 cells and value that are not even actual times I don't see a feasible solution with standard worksheet formulas.
Perhaps somebody else might see a way.

Would a vba solution be acceptable?

 

[Afaes]

Thanks for the sample data.
With 7 cells and value that are not even actual times I don't see a feasible solution with standard worksheet formulas.
Perhaps somebody else might see a way.

Would a vba solution be acceptable?

I am not familiar with vba. I was afraid that would be the case about addiding multiple cells to get the total hours. Would it matter any if I used 0700-1500 instead?

 

[Peter_SSs]

Would it matter any if I used 0700-1500 instead?

That would make it much easier

Afaes.xlsm
	A	B	C	D	E	F	G	H	I
1		Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Hours Worked
2	Employee 1	0700-1500	0700-1500	0700-1500		0700-1500	0700-1500		40
3	Employee 2		1330-2130		1330-2130	1330-2130	1330-2130	1330-2130	40
Hours (3)

Cell Formulas
Range	Formula
I2:I3	I2	=24*SUMPRODUCT(IFERROR(REPLACE(RIGHT(B2:H2,4),3,0,":")-REPLACE(LEFT(B2:H2,4),3,0,":"),0))

.. or easier still if you could use time format

Afaes.xlsm
	A	B	C	D	E	F	G	H	I
1		Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Hours Worked
2	Employee 1	07:00-15:00	07:00-15:00	07:00-15:00		07:00-15:00	07:00-15:00		40
3	Employee 2		13:30-21:30		13:30-21:30	13:30-21:30	13:30-21:30	13:30-21:30	40
Hours (4)

Cell Formulas
Range	Formula
I2:I3	I2	=24*SUMPRODUCT(IFERROR(RIGHT(B2:H2,5)-LEFT(B2:H2,5),0))