How to extract time from two dates in excel

0

i Start at 06/15/2017 21:30:18

i End at 06/17/2017 04:45:52

what are the total Number of Day Hours & What are the total Number of Night Hours, between these two dates?

My day is between 5:00:00 & 23:00:00 & My night is between 23:00:00 & 05:00:00 Please help.

Answer
Discuss

Answers

0
Selected Answer

I've included a template file that should work for you.

The basic formulas are these:

Get the total hours

=(A2-A1)*24

Get the hours for the day of the first date:

=MAX(0,(TIME(23,0,0)-TIME(HOUR(A1),MINUTE(A1),SECOND(A1)))*24)

Get the hours for the day of the second date:

=MAX(0,(TIME(HOUR(A2),MINUTE(A2),SECOND(A2))-TIME(5,0,0))*24)

Then use those numbers to get the results that you want. In the example, I put the dates in cell A1 and A2.

Discuss

Discussion

This is excellent but not working within the same day... please help.
masdigravi (rep: 1) Jul 27, '17 at 6:26 am
Please don't re-post the same question; I deleted the other one for you; if you had simply asked how to use the above formulas for time in the same day, it would not have been removed. I will look at this now for the same-day issue.
don (rep: 1492) Jul 28, '17 at 4:16 pm
Updated. Check it now.
don (rep: 1492) Jul 28, '17 at 4:28 pm
=MAX(0,(IF(DAY(A2)=DAY(A1),TIME(17,0,0),TIME(23,0,0))-TIME(HOUR(A1),MINUTE(A1),SECOND(A1)))*24)
i am unable to know the reason for putting TIME(17,0,0) in the above when my night hours start from 23,0,0
masdigravi (rep: 1) Aug 6, '17 at 7:13 am
I am not able to look at the whole worksheet right now, but did the formula work?
don (rep: 1492) Aug 6, '17 at 8:44 am
it is working only for times between 17-23
masdigravi (rep: 1) Aug 7, '17 at 9:44 pm
Thank you sir, i searched whole of teachexcel and found solution for my requirements.... great to the team of teachexcel for coming out with such a helpful website...
masdigravi (rep: 1) Aug 13, '17 at 8:06 am
Glad the site could help! And sorry I wasn't able to further help on here, but that's the nature of a free forum sometimes :/
don (rep: 1492) Aug 13, '17 at 1:47 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login