Yet another Time Question

[Gregory7]

Is it possible to calculate hours if both the start and end time are in the same cell. Unfortunatly the developers of our scheduling program developed it so when you export the schedule to excel it shows Greg in A1 and 3:00pm-9:00pm in A2. I would like to have excel calculate the 6 hours so I can use it to auto generate another spread sheet I am making.

 

[martindwilson]

well i think this is ott but

=TIMEVALUE(SUBSTITUTE(TRIM(MID(A3,FIND("-",A3)+1,7)),RIGHT(TRIM(MID(A3,FIND("-",A3)+1,7)),2)," "&RIGHT(TRIM(MID(A3,FIND("-",A3)+1,7)),2)))-TIMEVALUE(LEFT(A3,FIND("-",A3)-3)&" "&MID(A3,FIND("-",A3)-2,2))

 

[Gregory7]

Thanks for the quick response. So I used the formula for a 10:00am-5:00pm shift and it gave me a result of .29167 is there a format I need to change the output cell to?

 

[barry houdini]

Hello Greg, welcome to MrExcel

you need to format the result cell as h:mm

You could also try this formula, it should accommodate any times even if it crosses midnight like 9:00pm-5:00am

=MOD(REPLACE(REPLACE(A2,FIND("m",A2,FIND("-",A2)+1)-1,0," "),1,FIND("-",A2),"")-REPLACE(LEFT(A2,FIND("-",A2)-1),FIND("-",A2)-2,0," "),1)

 

[Gregory7]

Excellent now how do I use that number as an integer to divide a total sales goal by?

 

[martindwilson]

multiply the result by 24 format as general
=24*(MOD(REPLACE(REPLACE(A2,FIND("m",A2,FIND("-",A2)+1)-1,0," "),1,FIND("-",A2),"")-REPLACE(LEFT(A2,FIND("-",A2)-1),FIND("-",A2)-2,0," "),1))

 

[Gregory7]

Thank you guys for all your help with will definatly make things easier!

 

[Gregory7]

multiply the result by 24 format as general
=24*(MOD(REPLACE(REPLACE(A2,FIND("m",A2,FIND("-",A2)+1)-1,0," "),1,FIND("-",A2),"")-REPLACE(LEFT(A2,FIND("-",A2)-1),FIND("-",A2)-2,0," "),1))

Tried this one using a seperate wookbook and sheet here is my formula not getting a valid result any ideas? Just an fyi the orginal does work just not when copying between workbooks.

=24*(MOD(REPLACE(REPLACE([Sundaydt.xlsx]Page2!$D$10,FIND("m",[Sundaydt.xlsx]Page2!$D$10,FIND("-",[Sundaydt.xlsx]Page2!$D$10)+1)-1,0," "),1,FIND("-",[Sundaydt.xlsx]Page2!$D$10),"")-REPLACE(LEFT([Sundaydt.xlsx]Page2!$D$10,FIND("-",[Sundaydt.xlsx]Page2!$D$10)-1),FIND("-",[Sundaydt.xlsx]Page2!$D$10)-2,0," "),1))

 

[barry houdini]

The formula looks OK to me, are you sure there's data in that format in the referenced cell? What result do you get, an error?

 

[Gregory7]

Its pulling a time frame 7:00am-10:00pm..

Just getting #####

Edit* is it possible because it is listed at 07:00pm-10:00pm