Staff Attendance Calendar where text = values
I could be nuts, but am trying to devise a simple 12-month on a page excel calendar on which I can record employee attendance using a drop-down menu (with conditional formatting) for various absences.
For example, S is for Sick Days, T is for Tardy, V is for vacation, and s is for .5 of a sick day, t is for .5 of a tardy day, v is for .5 of a vacation day, etc.
OK, got the drop-down calendar in hand, now - I want to be able to calculate the sum of: all S days (where S = 1, s = .5); T days (where T = 1, t = .5), etc., and calculate the total for the various months on a quarterly basis (i.e. 3 months across the page), and ultimately on a yearly basis (i.e. 4 rows of 3 months each).
The quarterly formulas are stumping me, big time!
It might also be a problem that the capital/lower case difference may not be recognizable in the formula (i.e. that it might not be possible to differentiate between a capital 'S' and a lower case 's', in which case - I'll have to come up with some other text for the half day record).
I THINK some kind of 'SUMIFS' formula is required, but I don't have enough experience to work it through.
Can anyone help me?
Thank you!
Last edited by sporter; 07-24-2016 at 05:27 PM.
Reason: adding attachment - file
|