How to extract time from two dates in excel


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.



Selected Answer

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

The basic formulas are these:

Get the total hours


Get the hours for the day of the first date:


Get the hours for the day of the second date:


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



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: 1521) Jul 28, '17 at 4:16 pm
Updated. Check it now.
don (rep: 1521) Jul 28, '17 at 4:28 pm
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: 1521) 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: 1521) 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