#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
What is the value of AA, B, S, V W, M etc.?
|
#3
|
|||
|
|||
Try typing ="S"="s" in a cell. If you do that you'll see that excel doesn't differentiate between a capital or a lower case S.
In the attached I made a simple table that adds up the different instances for you. That you can then manipulate. It's often easier to break down the problem into steps and then solve each step on it's own. Later you'll learn how to solve the problems more efficiently in fewer steps. /Møller |
#4
|
|||
|
|||
All are valued at 1; I also need to use a series of codes for .5 values.
|
#5
|
|||
|
|||
The table you created is a great start for me, thank you. However - I still want to figure out a way to value the 1/2 day absences, and am looking for a formula that will add (for instance), all the V days (where V = 1) and all the vv days (where vv = .5) within a particular range of cells. (I understand now that I need a different kind of letter for the half days, and will need to change all the drop down formulas accordingly.)
Ideally - since I have (horizontally) - three months across, it would be something LIKE (obviously this is not even a pretend proper code.......): = SUM (A5:G10) + (I5:O10) + (Q5:W10), only V=1 and vv=.5 Is this possible? I'm finding conflicting online information about assigning values to letters, but - it can't be THAT hard (can it???)! Thank you! |
#6
|
|||
|
|||
Probably easier if you go Data base Access for this as you can expand greatly on it
|
#7
|
||||
|
||||
As you don't seem interested I will delete my previous post. Good luck
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#8
|
|||
|
|||
If I was not interested I would not have given a suggestion I was only trying to point you in a different direction as data basing is where you should be going have a look it good stuff.
|
#9
|
|||
|
|||
Quote:
I am frustrated at my own lack of knowledge and am eager to learn. |
#10
|
|||
|
|||
Quote:
I'm new to this forum. Thanks! |
#11
|
|||
|
|||
Ok so am I but seriously you should go to Access as it is easier to extract and input info and can be more user friendly and is more what you require. Do some online training you wont regret it. Took me a number of years but I got really good at it even bought the book and with the help of good friend built a fantastic employee data base which recorded what tools they used for how long what machines they operated where they were now and where they were then endless
|
#12
|
|||
|
|||
You need to identify how many of each instance there are of each type and then multiply with either 1 or 0.5. The sumproduct function can do that for you (https://exceljet.net/excel-functions...oduct-function), or you can augment the table that I created for you (see attached).
It's a bit difficult to understand what you are trying to achieve because if you just add up everything then the figure you get is essentially just days that aren't worked. I don't know how useful that is, because obviously a day on maternity leave isn't the same as a day where a person is just late. That beckons the question why would you then need to differentiate between the different types of non-working days. I fully agree with the others who suggest a database or use of a pivot table, however if you are somewhat new to excel then those options would be goals that you have to work towards. /Møller |
#13
|
|||
|
|||
Thank you, this helps enormously!
|
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Creating watermark macro for 400 staff | ryan1878 | Word VBA | 1 | 07-13-2016 02:45 AM |
Project as a Staff-tracking Tool | Kerry | Project | 1 | 03-01-2012 02:46 PM |
Automate Office Attendance | kgwack | Outlook | 0 | 02-14-2012 09:39 AM |
Calendar control accepts other values | JeJ | Word | 0 | 03-02-2011 03:38 PM |
Using MSP to predict staff resource | jarvo273 | Project | 0 | 05-13-2010 04:05 AM |