Excel Formula Help

[t_bannigan]

Hi there,

I need a formula to calculate the following:

The above times are times people are clocking in and out on one of my sites. I need a formula that will show the elapsed time outside the site hours e.g The site hours are 7:30-16:30, if I take the top row, they are showing as working 27 minutes over the site hours this will need to be deducted as I cannot put this forward to the client.
I was wondering if there is a formula out there that can help with this rather than having to go through every time individually and working it out!

Thanks in advance for any help!

 

[t_bannigan]

Are you using Excel 365?

Excel 2019 I believe

 

[Georgiboy]

I see, it states 365 on your about info so i made it for that, may not work in 2019

 

[t_bannigan]

I see, it states 365 on your about info so i made it for that, may not work in 2019

Ah apologies, I will change that now! Thank you again

 

[shinigamilight]

U can try this

=IF(REPLACE(A1,1,SEARCH(" ",A1),"")+0 > TIME(16,30,0),REPLACE(A1,1,SEARCH(" ",A1),"")+0 - TIME(16,30,0))+IF(REPLACE(A1,SEARCH(" ",A1),95,"")+0 < TIME(7,30,0),TIME(7,30,0)-REPLACE(A1,SEARCH(" ",A1),95,"")+0 )

 

[t_bannigan]

U can try this

=IF(REPLACE(A1,1,SEARCH(" ",A1),"")+0 > TIME(16,30,0),REPLACE(A1,1,SEARCH(" ",A1),"")+0 - TIME(16,30,0))+IF(REPLACE(A1,SEARCH(" ",A1),95,"")+0 < TIME(7,30,0),TIME(7,30,0)-REPLACE(A1,SEARCH(" ",A1),95,"")+0 )

Thank you for this. It works!

 

[Fluff]

In future please mark the post that contains the solution as the solution, instead of your feedback message as it will help future readers. No further action is required for this thread.

Also if you are using 2019 as per post#11 please change your profile to show this.
Thanks