# Calculating Time

#### Afaes

##### New Member

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 ??

### Excel Facts

Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Welcome to the MrExcel board!

Is this what you want?

21 12 12.xlsm
AB
1ScheduleHours
27am-3pm8
Hours
Cell Formulas
RangeFormula
B2B2=LET(t,SUBSTITUTE(SUBSTITUTE(A2,"a"," a"),"p"," p"),x,FIND("-",t),24*(REPLACE(t,1,x,"")-LEFT(t,x-1)))

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

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.

21 12 12.xlsm
AB
1ScheduleHours
27am-3pm8
Hours (2)
Cell Formulas
RangeFormula
B2B2=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))

It worked! Thank you!

You're welcome. Thanks for the confirmation.

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

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

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?

Replies
2
Views
168
Replies
2
Views
162
Replies
1
Views
61
Replies
4
Views
153
Replies
3
Views
142

1,203,521
Messages
6,055,885
Members
444,830
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back