Calculate hrs and mins

[#1]

Data set is given below:

Close Datetime                 Start Datetime                  Total HH:MM   Req HH:MM

6/23/2011   8:46:34 AM     5/22/2011 6:48:11 PM       757.97              176.00

7/25/2011 11:16:45 AM     5/23/2011 8:07:36 AM      1515.15              362.27

12/26/2011 7:48:56 PM     5/23/2011 9:30:55 AM      5218.30              1247.50

Requriment result is in Red

Condition is hh:mm calculate base 9:00am to 5:00pm working hrs and min,

not includes saturday, sunday and other hrs and min.

for example: 5/21/2012 4:00pm is start time and 5/22/2012 10:00am is close time, so result is 2:00hrs.

5/21/2012 3:45pm is start time and 5/22/2012 10:30am is close time, so result is 2:45hrs.

5/18/2012 3:00pm is start time and 5/21/2012 11:00am is close time, so result is 4:00hrs. not used sat & sun

[john_duffy]

Hello.

Here's the basic logic I use to accomplish this type of task:

Set a variable to calculate the number of business days between the two dates:

eg. Set vNetBusinessDays = networkingdays(Close Date,Start Date)

      Note: You can also include holidays in the networkingdays function if you want to ignore them.

Set two variables for start and end working times:

eg. Let vStartTime = frac(Maketime(9))

     Let vEndTime = frac(Maketime(17))

Set a variable for the number of hours in a working day:

eg. Set vEightHours = 0.333333333333    // interval (8/24)

Calculate the hours and minutes between the two Dates/Times:

If Start Date = Close Date --> interval(Close Date Time - Start Date Time)

If Start Date < Close Date  --> Interval ($(vEndTime - frac(Start Date Time)) // hrs/mins between Start and 5:00 pm

                                        + Interval(frac(Close Date Time) - $(vStartTime)) // hrs/mins between 9:00 am and End

                                        + (vNetBusinessDays - 2) * $(vEightHours) // number of full days between the        

                                                                                                          Start and End

On your chart, set the Number Format Setting to Time (hh:mm).

The above code has not been fully tested.  It should give you an idea for one method of calculating the hours/minutes between two dates.

Hope this helps,

John.

[Jason_Michaelides]

You need to use NetworkDays plus more logic for the partial days....

(NetWorkDays([Start DateTime],[Close DateTime])          //FULL working days between two dates, inclusive

                    - IF(Frac([Close DateTime])<Time#('17:00','hh:mm'),1,0)          //if close day isn't complete subtract 1 from NetWork days

                    - IF(Frac([Start DateTime])>Time#('09:00','hh:mm'),1,0)          //if start day isn't complete subtract 1 from NetWork days

          )*8          //multiply by 8 hours per day.  We now have the number of hours for all the FULL days between the dates

                    + ((Time#('17:00','hh:mm') - Frac([Start DateTime]))*(1/24))          //add on the time the ticket was open on the first day (result is a fraction of a day so multiply by 1/24 to get hours)

                    + ((Time#('17:00','hh:mm') - Frac([Close DateTime]))*(1/24))          //add on the time the ticket was open on the last day (result is a fraction of a day so multiply by 1/24 to get hours)

          AS          WorkHours

See attached,

Hope this helps,

Jason

[Jason_Michaelides]

Actually - this is almost right but there's a slight glitch somewhere...I'll find it

[Jason_Michaelides]

Here's the correct one (I believe):

(NetWorkDays([Start DateTime],[Close DateTime])          //FULL working days between two dates, inclusive

                    - IF(Frac([Close DateTime])<Time#('17:00','hh:mm'),1,0)          //if close day isn't complete subtract 1 from NetWork days

                    - IF(Frac([Start DateTime])>Time#('09:00','hh:mm'),1,0)          //if start day isn't complete subtract 1 from NetWork days

          )*8          //multiply by 8 hours per day.  We now have the number of hours for all the FULL days between the dates

                    + RangeMax(((Time#('17:00','hh:mm') - Frac([Start DateTime]))*24),0) //add on the time the ticket was open on the first day (result is a fraction of a day so multiply by 24 to get hours). RangeMin() as if the start time is after 5pm this will be negative.

                    + RangeMax(((Frac([Close DateTime]) - Time#('09:00','hh:mm'))*24),0) //add on the time the ticket was open on the last day (result is a fraction of a day so multiply by 24 to get hours). RangeMin() as if the end time is before 9am this will be negative.

          AS          WorkHours

See attached,

Jason

[#6]

Shouldn't your 176 hour answer be 184 hours.

5/22 is sunday No Hours

5/23 to 5/27  40 Hrs

5/30 to 6/3    40 hrs

6/6 tp 6/10    40 hrs

6/13 to 6/17  40 hrs

6/20 to 6/22  24 hrs

6/23 -closed befor 9:00 - no Hours

total is 184?? What am I missing?

Stephen

[Jason_Michaelides]

Not sure if that question was aimed at me, Stephen but you have certainly highlighted an error in my script! Where I am adding and subtracting from NetworkDays() I should be performing a check and only do it if a weekday!

Will fix later...

[#8]

Attached is a solution.

I first calculate NetWorkingDays Hours without the Start Day or the Closing Day.

Then I calculate the Start Day and Closing day time using the variables for 9:00 AM and 5:00PM. I also test to see if the Start Date or Closing Date is a Sat or Sun.

Then I add the sum of the Start Day and Closing Day hours plus any Start Day plus Closing Day minutes that round up to 1 hour.

I then calculate the remaining minutes.

I can see that I have a problem if a Start Date and Close Date are on the same date (will have to work on that).

I have left a lot of mid point calculations in the example.

HTH,

Stephen

[#9]

Thanks to all to share your knowledge for my requriment, i will check today and let you know if i have any issue

Regards,

Zain.

[#10 · Accepted solution]

I have attached the final calculations which now take into account a start date and close date being the same day.

I have also add some notes about that each part of the scrip it doing.

Stephen