Support with WTD, MTD & QTD figures.

[Cutchy]

hi,

Guys i'm new to this site and also not excellent on excel. I need supoprt on how to calculate the above.
I am completing a sickness tracker and have the raw data dumped into excel. Due to our applications I initially calculate their sickness %age this on its own requires the use of conditional sums. I breakdown the data to department, manager and advisor but I also need to work out Daily, WTD, MTD and QTD figures. I have been using the conditional sum tool to filter the sickness figures but I don't want to rebuild the report every week and month etc amending the date ranges. Can someone please help.

fYI - The data is broken down as column -
A- date
d- name
e- manager
g- dept
j- hrs sick

 

[sadi]

If u want to Sum Catagory Wise, try this.

=SUMPRODUCT(--($A$1:$A$6=Number),--($B$1:$B$6="Text")

 

[Cutchy]

sadi

I requier the formula to calulate Daily, WTD, MTD and QTd figures

So for example my equation calculates the sickness %age using the formula below. I simply want to insert to pick up the Daily, WTD, MTD and QTd figures

=SUM(IF('Gen bank'!$G$2:$G$2871="Gen Bank",IF('Gen bank'!$H$2:$H$2871="Left",'Gen bank'!$J$2:$J$2871,0),0))

 

[Norie]

What are 'Daily, WTD, MTD and QTd figures'?

PS I'm pretty sure I know what you mean, but using acronymns/abbreviations doesn't make it easy to help.

It might also be an idea to post some sample data and expected result.

PS Have you looked at Data>Pivot table...?

As far as I can remember that's a pretty good way to summarise data and has various options to group the data.

 

[Cutchy]

?? Daily, week to date, month to date, quarter to date

 

[sadi]

Can you post your Sheet Please

 

[Cutchy]

Can't post the sheet due to info in there but I'll explain to the best of my ability. Basically I get an automated data dump which brings back all exceptions for evry advisor over a set date range.
Column - Catergory
a - Date
d- staff name
e- team leader name
f - senior team manager name
g- department
h- exception code
j - no of hours (for the exception)

The idea is to build a tracker around this. what I need to do is:-
- Calculate the Sickness percentage for each advisor. I initially need to totalthe exceptions in hours and remove what is not viewed as part of their scheduled hours (such as unpaid breaks etc). Then I can use this figure to calculate sickness %age.
- Once complete I need to filter to Team Leader, STM and Dept level.
- this all needs to be viewed as daily, Week to date, month to date, quarter to date.

Hope that makes sense