Hi All,
I'm helping out my team in putting together a vacation schedule.
I have a spreadsheet counting Vacation (AV) days and Sick (S) days.
I know how to setup the formula for full days but how about partial days. We have them writing down their partial days as
AV/3.5 = this meaning 3.5 hours off
S/2 = 2 hours sick.
For full days its just AV or S
formula I'm using
=countif(A1:Z1,"AV") - count full days.
thanks in advance!!!
Ray
You can use a conversion table that converts each of your known text strings into a specific number of hours. Then an array formula can count each of those entries and sum the hours, then divide by 8 to get the "day count".
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Thanks JBeaucaire! you rock!
There's been some slight adjustments to the spreadsheet but I can't seem to work the kinks out.
The layout of the entries is Horizontal and for the life of me can't seem to figure out.
Here is how it would look like.
vacay.jpg
You have a workbook for me to look at showing this problem?
Here you go.
Once again much appreciated!
1) That workbook is missing the reference table I showed you.
2) That workbook is missing your attempt to edit the formula from a column to a row.
I'm trying to see what you misunderstood about the formula by seeing how you've tried to edit it.
here you go.
Array formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
So, click on AH6, press F2 to edit the formula, then CTRL+SHIFT+ENTER confirm the formula and activate the array.
The formula should actually be: =SUM(SUMIF($AM$6:$AM$23, $C6:$AG6, $AN$6:$AN$23))/7.5
Then copy AH6 down as needed.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks