Formula to calculate effort hours.


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.



Please dont make duplicate posts. Your other post has been deleted.
don (rep: 1665) 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
Add to Discussion



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. 


Answer the Question

You must create an account to use the forum. Create an Account or Login