# Formula to calculate effort hours.

0

Suppose my  working hour  is 9 am till 6 pm....

1 ticket loged  on    1st june  at 3 pm..

and got resolved on   6th June at  11 am...

Time taken to resolve ticket :

1st june  = 3 working hours     (3 pm to 6 pm )

2nd june  = 9 working hours

3rd june  = 9 working hours

4th june  = 9 working hours

5th june  = 9 working hours

6th june  = 2 working hours   ( 9 am to 11 am )

For me it took 41 working hours to resolve.

Is there any formulae in Excel.

So that I can  put Logged date and time  and resolved date and time

And get no of hours given to ticket during working hours.

### Discussion

don (rep: 1412) Jun 6, '17 at 8:58 am
May it be assumed that once one starts a ticket that one works on the same until completion?
gebobs (rep: 30) Jun 6, '17 at 10:05 am

0

Is there any formulae in Excel.

YES several.

So that I can  put Logged date and time  and resolved date and time

Perhaps you would track the tickets in their own tab/worksheet.  Fields may include:

TicketNo;   StartDate&Time; EndDate&Time; Status(A-Active, C-Completed); Total TimeWorkedOn;  .  You could run a Pivot table on the data to Summarize the tickets and totals (for those that are completed) .  The original data could be filtered to tell you the Final date worked on by Filtering on Completed column.