Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Count periods

0

Hi, 

I need help with an absence tracker! I would like to have a formula that show amount of periods the employee has been absence. 

like i have the name of the employee (Mike) in colum A2  and after that to S2 all days in the month. 

so say that Mike is sick for 3 days that i mark (S) in cell B2 - C2 - D2 thats is 3 days but one period. 

But mike is also sick 2 more days that month that i mark (S) in cell H2 - I2 thats 2 days and 1 period. 

so what i need help with is the period part, what kind of formula will help me with this? 

Thanks in advance // Fredrik 

Answer
Discuss

Discussion

You haven't given enough information on the periods. One time three days equals 1 period and another time two days equals 1 period...
don (rep: 1989) Aug 8, '16 at 4:16 pm
Hi, 

if i could attach a picture of the sheet it would be easy to explain..... :) 
its a simple tracker where i have employee name in C12 and dates from E12 to Y12 representing first of July to end of July. employee is sick 4-5-6 July 1 period of 3 days, and also 18-19 July 1 period and 2 days. I have marked days with S for Sick. 

I have used countif to count amount of S days total in Z12 that gives me 5 days. But really need support how to calculate the periods in AA12??? it's 2 periods in total.

Thanks in advance // Fredrik 
Fredrik860218 (rep: 2) Aug 8, '16 at 6:30 pm
Ok, I think my answer should do the trick.
don (rep: 1989) Aug 8, '16 at 9:56 pm
Add to Discussion

Answers

0
Selected Answer

Insert a new row and enter this formula:

=IF(AND(B2="S",C2<>"S"),1,"")

You would put this formula into cell B3; if it needs to go somewhere else, adjust the cell references in the formula accordingly.

Copy it for the entire date range and then use a simple count formula on that row.

If you don't want to view this new row, just hide it - right-click > hide.

Update

Your sample file with the formula I listed above is now included.

Discuss

Discussion

I can't get it work. Can I send you the sheet so you can try? 
Fredrik860218 (rep: 2) Aug 9, '16 at 8:00 am
You can edit your original question and upload the file there. I will get a notice when you update the question.
don (rep: 1989) Aug 9, '16 at 11:15 am
Okey, did not know that. its attached! thanks
Fredrik860218 (rep: 2) Aug 9, '16 at 12:13 pm
Take a look at the file now.
don (rep: 1989) Aug 9, '16 at 2:00 pm
Don, your a star!! Thanks
Fredrik860218 (rep: 2) Aug 10, '16 at 7:09 am
Hi Done, i have one more question (VBA Code) see attached. 
Fredrik860218 (rep: 2) Aug 10, '16 at 4:57 pm
You don't need VBA code to sort that. Just select the Periods and go to the Data tab and click the AtoZ or ZtoA sort button. You can sort everything at once to keep it in the same row by selecting all of the data and then sorting by the Periods column.
don (rep: 1989) Aug 11, '16 at 11:20 am
Add to Discussion


Answer the Question

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