Calculating the working hours based on Today's date and Next day

[santhoshkunder]

Happy New Year to All...

I need to derive the the working hours based on the specific time interval. I have explained my problem in the attached workbook. I need a formula to achieve. I have tried my best but with no luck.your help on this is greatly appreciated.
Thanks
santhosha

 

[vivekgoyal]

Hi @santhoshkunder,
Wish you a very happy new year!

Here goes my attempt, I have edited cell I1 with 10:00:00 AM, hope that's fine

=IF(AND(A2-ROUND(A2,0)+1>$G$1,A2-ROUND(A2,0)<$I$1),"Beyond ","Within ")

Cheers,
Vivek

 

[santhoshkunder]

You are a life saver. Million thanks to you. One last help. Can we have a one more column in front of it which should show me the exact date of resultant row. What it means is though the formula is showing Next day date but i want to consider it for previous day.Say for example 9/26/2015 12:26:00 AM actual date is 09/26 but for me i want to show date 09/25 because it has not crossed our end time 10:00:00 AM.
Thanks
santhosha

 

[vivekgoyal]

Hi @santhoshkunder,
Apology for delay in reply.. here you go
=ROUND(IF(A2-ROUND(A2,0)<$I$1,A2-1,A2),0)

Cheers!
Vivek