Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

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.

excel
MS Office Version: Office 10
posted by: masdigravi (rep: 1) on Sat Jul 22, 2017 at 6:49 am
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.

posted by: don (rep: 1989) on Sat Jul 22, 2017 at 2:44 pm
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: 1989) Jul 28, '17 at 4:16 pm
Updated. Check it now.
don (rep: 1989) 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: 1989) 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: 1989) 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