Hi guys, I need to bother you once again
Say I have 2 times e.g
D8= 14:00
E8= 22:00
I need a formula to count how many hours between D8 and E8 is between 18:00 and 6:30
In this example its 4 hours (18:00 till 22:00)
If D8 was 19:00
and E8 was 5:00,
then the formula result should be 10 hours (19:00 till 5:00)
another example
If D8 was 4:00
and E8 was 11:00
formula result should be 2.5 hours (4:00 till 6:30)
I would also appreciate if you can explain the formula, so I can learn from it.
In your example...
If D8 was 19:00
and E8 was 5:00
i presume you mean 5:00 am the next day?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
deleted my duplicate post
Yes FDibbins.....19:00 is 7 O' clock PM.......5:00 is 5 O'clock Am the next day
It's easier to claculate the hours during a period which doesn't pass through midnight, so to get hours 06:30 to 18:00 you can use this formula
=((E8<D8)*("18:00"-"6:30")+MEDIAN(E8,"6:30","18:00")-MEDIAN(D8,"6:30","18:00"))*24
so to get 18:00 to 06:30 hours you can sinply subtract that from the total hours, i.e.
=(MOD(E8-D8,1)-(E8<D8)*("18:00"-"6:30")-MEDIAN(E8,"6:30","18:00")+MEDIAN(D8,"6:30","18:00"))*24
Audere est facere
Daddylonglegs.....
Can you please explain what exactly both of those formula is doing.
I don't wanna just copy paste.
Last edited by LionelSpratt; 02-11-2013 at 11:58 AM.
Hi LionelSpratt
Maybe if you have the date stated as well, it will be more clear?
For example, 2/13/2013 19:00 to 2/14/2013 5:00.
A2=2/13/2013 19:00 --> Start Date & Time
B2=2/14/2013 5:00 --> End Date & Time
=TEXT(B2-A2,"[h]:mm")
You will get 10hours as the result.
Is this what you need? Hope it helps (^-^)
Hikari......
Thanks for your effort, but it doesn't give me what I need.
The formula that Daddylonglegs posted works perfectly
I just don't understand the formula
And I want to understand so next time i can do it myself, or adapt it if I have to.
I hope someone can explain what the formula does exactly
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks