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]

 

[Peter_SSs]

Welcome to the MrExcel board!

Is this what you want?

21 12 12.xlsm
	A	B
1	Schedule	Hours
2	7am-3pm	8
Hours

Cell Formulas
Range	Formula
B2	B2	=LET(t,SUBSTITUTE(SUBSTITUTE(A2,"a"," a"),"p"," p"),x,FIND("-",t),24*(REPLACE(t,1,x,"")-LEFT(t,x-1)))

 

[Afaes]

Thank you- When I tried that formula this appeared in cell B2: #NAME?

 

[Peter_SSs]

When I tried that formula this appeared in cell B2: #NAME?

Sounds like you do not have the LET function in your copy of MS365.
Try this instead then.

21 12 12.xlsm
	A	B
1	Schedule	Hours
2	7am-3pm	8
Hours (2)

Cell Formulas
Range	Formula
B2	B2	=24*(REPLACE(SUBSTITUTE(SUBSTITUTE(A2,"a"," a"),"p"," p"),1,FIND("-",SUBSTITUTE(SUBSTITUTE(A2,"a"," a"),"p"," p")),"")-LEFT(SUBSTITUTE(SUBSTITUTE(A2,"a"," a"),"p"," p"),FIND("-",SUBSTITUTE(SUBSTITUTE(A2,"a"," a"),"p"," p"))-1))

 

[Afaes]

It worked! Thank you!

 

[Peter_SSs]

You're welcome. Thanks for the confirmation.

 

[Afaes]

One more question if you don’t mind. How would I get the total hours from Sunday - Saturday (all working 7am-3pm) to say 56hrs instead of 8??

Thank you

 

[Afaes]

For instance, A2 is Sunday, then B2 is Monday and so forth. How would I get H2 to say 56hrs?

 

[Peter_SSs]

Can you show us (preferably with XL2BB) some actual sample data and expected result so that we can be sure of the layout and format of it?