Hi There,
My question as follows;
1. Open date: 1/2/2010 5:22pm
2. Closed date : 2/2/2010 2:22pm
Hours to exclude in the calculation is 12am till 6am
Basically, i am trying to calculate the time difference in hours between Open date and Closed date and excluding 12am till 6am.
Answer should be (if i calculated it correctly): 13hours and 54 minutes.
Thanks in advance
I don't get why it is 13:54, looks like 15:00 difference to me.
If I am correct, you can use
=MAX(0,NETWORKDAYS(A2,A3)*("24:00"-"06:00")
-IF(NETWORKDAYS(A2,A2),(MEDIAN(MOD(A2,1),"24:00","06:00")-"06:00"),0)
-IF(NETWORKDAYS(A3,A3),("24:00"-MEDIAN(MOD(A3,1),"24:00","06:00")),0))
Ooppps i may have calculated it incorrectly. my bad.Originally Posted by Bob Phillips
Thank you..
I've updated the formula to my sheet
"=MAX(0,NETWORKDAYS(I8,J8)*("24:00"-"06:00")
-IF(NETWORKDAYS(I8,I8),(MEDIAN(MOD(I8,1),"24:00","06:00")-"06:00"),0)
-IF(NETWORKDAYS(J8,J8),("24:00"-MEDIAN(MOD(J8,1),"24:00","06:00")),0))"
I8 = 14/12/2019 10:30 PM
J8 = 15/12/2019 01:32 PM
Doesn't networkdays exclude weekends? if yes, i need to include that as well. The only thing to exclude in the calculation is the hours from 12am to 6am.
Last edited by AliGW; 01-05-2020 at 01:13 PM. Reason: Please don't quote unnecessarily!
Is there only ever a maximum of one day difference between Open & Close dates or could the gap be several days?
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
The gap between the days could be a day or several days apart and it could also be on the days itself. Example, open 1/1/2010 at 10am and closed on 1/1/2010 at 3pm.
Last edited by AliGW; 01-05-2020 at 01:12 PM. Reason: Please don't quote unnecessarily!
Here's a UDF that will do what you want...
Use it in a cell and format it as "[h]:mm"
Please Login or Register to view this content.
wow this is awesome. and thanks a bunch.. i'll give it a try and report backHere's a UDF that will do what you want...
Use it in a cell and format it as "[h]:mm"Please Login or Register to view this content.
It works Juddaaaa - thank you very much !!!!!Here's a UDF that will do what you want...
Use it in a cell and format it as "[h]:mm"Please Login or Register to view this content.
hi juddaaaa
The UDF works but if gives me ########## when the close date falls within 12am to 6am...
Open: 11/12/2019 22:53
Close: 12/12/2019 4:51
I noticed that all those closed within 12am to 6am is giving me the ######## (message is dates and times that are negative or too large to show as #####
the answer should be 1.07, right?
Try this
Please Login or Register to view this content.
Last edited by juddaaaa; 01-06-2020 at 07:30 PM.
Thanks again!!!
I've noticed, if the open time falls within 12am to 6am, the calculation will include the hours within 12am to 6am.
example,
open 7/12/2019 2:43am
close 7/12/2019 9:28am
answer = 6:45
This happens if the open date is within 12am to 6am. if it was outside those hours, the calculation is perfect.
By the way, can we change reading from 6:45 to 6.45?
This should do it
Open = 7/12/2019 2:43amPlease Login or Register to view this content.
Closed 7/12/2019 9:28am
Answer = 3.28
To get 3.28 change the format of the cell from [h]:mm to [h].mm
Last edited by juddaaaa; 01-07-2020 at 09:23 PM.
Awesome !! it works now.. thank you very much...This should do it
Open = 7/12/2019 2:43amPlease Login or Register to view this content.
Closed 7/12/2019 9:28am
Answer = 3.28
To get 3.28 change the format of the cell from [h]:mm to [h].mm
POST can be changed to RESOLVED
Last edited by Qhamzarul15; 01-08-2020 at 02:58 AM. Reason: update post
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
Dave
Thank you. I was wondering how to mark the thread as solve
This is how you do that:
please select Thread Tools from the menu link above and mark this thread as SOLVED.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks