Excel Formulas accumulate days

[thifu3]

Hello

I want a formula that if an employee works 6 days 1 off day, 12 days,2 off days.

when i use the following formula, it shows 6 days plus then 2 off days.

=TRUNC(((D12)+6)/7)+(WEEKDAY(B2)=WEEKDAY(C11))


d12 is total days worked.

B2 today().

C11 starting day..

 

[SirJB7]

Hi, thifu3!


At the risk of being wrong with a very simple formula, have you tried this formula?

=INT(D12/6)

It shows 0 for days from 1 to 5, 1 for 6 to 11, 2 from 12 to 17, and so on.

A question: what are the B2 and C11 values used for?


Regards!

 

[oldchippy]

Hi,


Using your other dates, maybe


=ROUND((B2-C11)/6,0)

 

[thifu3]

@ siraj. actually i want a formula that my staffs work for 6 days they can accumulate 1 off day. like that 12 days they can get 2 days. B2 is today's date and C11 is used to enter commence date of employee. i think your simple formula is working. any advise, please give.

 

[Faseeh]

Hi thifu3,


You need to add the total days worked into the value that SirJB7's and/or oldchippy's formula! You can also use (a one step longer)

FLOOR(D12/6,1) that works equally. The final result must be 


[code]D12+INT(D12/6) OR

D12+ROUND((B2-C11)/6,0)

OR

D12+FLOOR(D12/6,1)[/code]


Regards,

Faseeh

 

[SirJB7]

Hi, thifu3!


Please give a look at this file: http://dl.dropbox.com/u/60558749/Excel%20Formulas%20accumulate%20days%20%28for%20thifu3%20at%20chandoo.org%29.xlsx


If I'm not missing anything really big... it works.


Regards!