• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculate Working time between two date,time.

vamsi

vamsi

New Member
Hi,
I am calculating the working time (8:30 - 5:30) from two different dates as below.
in the below case we should get 9:00 as a difference for first line. help me in solving using the formula.

Created Resolved
3/31/2014 4:05 am 4/1/2014 3:18 am
4/1/2014 6:32 am 4/3/2014 6:18 am
3/26/2014 9:39 am 4/3/2014 6:20 am
3/31/2014 12:05 pm 4/3/2014 6:43 am
3/26/2014 8:04 am 4/3/2014 7:02 am
3/13/2014 5:44 am 4/3/2014 7:03 am
 
vamsi said:
Hi,
I am calculating the working time (8:30 - 5:30) from two different dates as below.
in the below case we should get 9:00 as a difference for first line. help me in solving using the formula.

Created Resolved
3/31/2014 4:05 am 4/1/2014 3:18 am
4/1/2014 6:32 am 4/3/2014 6:18 am
3/26/2014 9:39 am 4/3/2014 6:20 am
3/31/2014 12:05 pm 4/3/2014 6:43 am
3/26/2014 8:04 am 4/3/2014 7:02 am
3/13/2014 5:44 am 4/3/2014 7:03 am
Click to expand...
Hi,

Is this difference to include Saturdays and Sundays?
 
@vamsi - Try this...

Assume - Start date & Time is in Cell A1 and End date & time is in Cell B1...then put the following formula in Cell C1

=(NETWORKDAYS(A1,B1)-1)*("17:30"-"8:30")+IF(NETWORKDAYS(B1,B1),MEDIAN(MOD(B1,1),"17:50","8:30"),"17:30")-MEDIAN(NETWORKDAYS(A1,A1)*MOD(A1,1),"17:30","8:30")

Note - It excludes the weekends...

Let me know if you need something else...
 
Hi,
I am getting incorrect calculation
Created
2/20/2014 8:46
Resolved
4/10/2014 4:51
for this the result is 2:44, however the result should be 332 hours.
 
Select the cell C1..where you pasted the formula..then follow the below steps
1) Press CTRL + 1
2) Select the number tab on the top of the pop up
3) Then in the catefory list..select custom(last option)
4) On the right hand side of the pop up...in "Type" box..paste [h]:mm:ss;@
5) Click OK..

It should work..and can you also double check for the above timelines...is it 332 hours or 314:44....assuming both are morning timings..
 
Hi Asheesh,
Thank you very much it is working by changing the format of the cells.
Thankyou Chandoo.org for having experts as members.
 
You must log in or register to reply here.
Back
Top