Time Formula Help

[JennyGP]

Hello All -
I need to calculate any hours between 8:00PM and 6:00AM

Example:
Start Time:10:00PM
End Time:6:30AM
Answer: 8 hours

Please help me.

Thank you.
Jenny

[:) Sixthsense :)]

Not sure how you are arriving 6 hours?

[JennyGP]

Sorry - I meant 8 hours
Thank you.

[:) Sixthsense :)]

Try this...

A1 cell
10:00 PM

B1 cell
6:30 AM

In C1 cell

Please Login or Register  to view this content.

[dilipandey]

Hi JennyGP,

Still answer would be 8:30 hours as per your example:

Example:
Start Time:10:00PM
End Time:6:30AM
Answer: 6 hours

Use below formula:-

=IF(B2<A2,("24:00:00"-A2)+B2,B2-A2)

where A2 is start time and B2 is End time

See attached Time diff.xlsx


Regards,
DILIPandey

<click on below 'star' if this helps>

[JennyGP]

Thank you -
One more question the time format should be as an example 5:00 PM or 17:00? just 5:00
I actually have the raw data showing one column 5:00 and the other column shows AM or PM

[dilipandey]

Hi Jenny,

These all are time formats.. you can format the cell as you want. Select the cell, press ctrl + 1, and choose the required format under Time category

Regards,
DILIPandey

<click on below 'star' if this helps>

[JennyGP]

I need to calculate the hours that fall between 8PM and 6 AM so the answer would have to be 8 hours

Please help

[dilipandey]

calculate the hours that fall between 8PM and 6 AM so the answer would have to be 8 hours

as per me.. result should be 10 hours .. please check. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[JennyGP]

Hours between 8pm and 6am is total of 10 hours however i am trying to capture the hours within different time periods
example

start: 10:00PM
end: 630AM
Answer 8 hours

start: 7:00 PM
end: 6:30 PM
ANswer: 11 hours

[dilipandey]

M confused.. I surrender

Regards,
DILIPandey

<click on below 'star' if this helps>

[JennyGP]

Thank you for trying

[zbor]

If your times are in A1 and B1 try:

=MOD(B1-A1, 1)

[JennyGP]

IT gives me total hours in a clock in and out period but i only need to count the hours the fall between 8 pm and 6 am

[zbor]

try this:

Please Login or Register  to view this content.

[JennyGP]

It was almost perfect -
I must be doing something worng

When i have start 20:30 and end of 22:30 it is calculating 10 hours and not 2:30 hours

[JennyGP]

SOC.xlsx

Hope this works -
I am trying to attach a portion of my data that i need help with.
TY