I would appreciate greatly help with a formula to count consecutive days worked by an employee(no more than 13 consecutive days), BUT it also needs to reset count when employee misses a day. If possible, having formula dynamic and perform count from current date(which changes daily).. would be great.
In attached sample, cells with start time are days worked, blank cells represent days not worked.
Thank you
formula is too long to be translated to English
Last edited by tim201110; 09-17-2018 at 08:47 AM.
Assuming results in Column AE, and I have interpreted the requirement correctly... below Array formula should present lesser of 13 days and longest sequence.
Formula:
AE2:
=MIN(13,MAX(FREQUENCY(IF((D2:AC2>0)*(D2:AC2<>""),COLUMN(D2:AC2)),IF((D2:AC2=0)+(D2:AC2=""),COLUMN(D2:AC2)))))
entered with CTRL + SHIFT + ENTER
Note: formula array entry - if applied correctly formula will appear encased within { } [these cannot be added manually]
XLent,
Thanks for your help.... if possible, results should be in "Days Worked" column. Correct me if I'm wrong.. w/out formula considering current date, I would have to delete column D daily in order to keep the 13 day count accurate. It would be awesome if formula would count days based on current date. I don't know if that's possible. Thanks again..
Last edited by jeffreybrown; 09-18-2018 at 10:30 AM. Reason: Removed full quote!
Thank you for your help. Having trouble copying and pasting formula. When I remove "protected mode" so I can copy formula, gives below error:Originally Posted by tim201110
Great help
Without wanting in interfere, as I suspect tim201110 has your solution, can you clarify the exact requirement.... more specifically, are you looking for most consecutive days worked from today, backwards, up to a maximum of 13 days, or longest working streak in the last 13 days? e.g. an employee works 10 days, has 1 day off, then works both yesterday & today - the answer is a) 2 ... most consecutive days worked backwards from today, or b) 10 -- the most consecutive days worked in the past 13 days ? Clearly a) makes most sense but best to never assume...
XLent,
An employee cant work more than 13 consecutive days, must have that 14th day off. This count resets when a day isn't worked, whether the 14th or any day prior.
Last edited by jeffreybrown; 09-18-2018 at 10:30 AM. Reason: Removed full quote!
shorter formula with helper column
it works for row3 and down
row1 is new
and formula calculates the whole month
=IFERROR(MAX(FREQUENCY(IF(ISNUMBER(INDEX($E3:$AI3,$C3):INDEX(INDEX($E3:$AI3,$C3):$AI3,$C$1)),COLUMN(INDEX($E3:$AI3,$C3):INDEX(INDEX($E3:$AI3,$C3):$AI3,$C$1))),ISBLANK(INDEX($E3:$AI3,$C3):INDEX(INDEX($E3:$AI3,$C3):$AI3,$C$1))*COLUMN(INDEX($E3:$AI3,$C3):INDEX(INDEX($E3:$AI3,$C3):$AI3,$C$1)))),) CSE
tim201110,
tim.... on the work sheet, column C shows 17 and column D shows 0. I don't see the count of days worked...? columns E-H(no days worked) I-S(show start times/days worked) blanks are days not worked.
Last edited by jeffreybrown; 09-18-2018 at 10:31 AM. Reason: Removed full quote!
In case it helps, and if I've understood requirements, below would look for most days worked back from today
Formula:
=IF(INDEX($D2:$IV2,$A$24)="",0,TODAY()-LOOKUP(2,1/($D2:INDEX($D2:$IV2,$A$24-1)=""),$D$1:INDEX($D$1:$IV$1,$A$24-1)))
Please note that reference above to $A$24 is a cell containing the following -- and is used to negate need for duplication of calc {the same could be stored in a Named Range etc}
Formula:=MATCH(TODAY(),$D$1:$AV$1,0)
Note: to test I had to extrapolate your sample data such that there was activity on today's date... it's also unclear what you would want to do if the answer exceeded 13 {I believe you said this could not happen and have thus ignored it, per se}
if neither solutions are doing what you want it might make sense to post a further sample, alongside expected results in each case.
Last edited by XLent; 09-18-2018 at 03:26 AM.
XLent,
I'm sorry... I thought it was clearly stated. My bad. An employee cant work more than 13 consecutive days, if so... he must take off 14th day. Any day off(whether scheduled or missed)restarts the 13 consecutive day count. If the cell in Days Worked is greater than 13, it will be ok. We just have to make sure the person gets a day off asap. Does this clear up the "ignored" expectation?
Last edited by jeffreybrown; 09-18-2018 at 10:31 AM. Reason: Removed full quote!
Probably overcomplicated, but seems to do the job.
It's array formula (Ctrl+Shift+Enter committed):
Formula:=TODAY()-IFERROR(LARGE(IF(OFFSET(D2,0,0,1,MATCH(TODAY(),D$1:AC$1,0))="",OFFSET(D$1,0,0,1,MATCH(TODAY(),D$1:AC$1,0)),""),1),D$1)
See attached
PS. If opened tomorrow will show only 0's as tomorrow column is empty
Best Regards,
Kaper
Hello Kaper,
I'm looking at the Zeszyt2 file and your formula 492 work days. I'm not sure if your formula is working properly. However, I have facing with the same issue as csweezy and I need your excellent help, and I would appreciate your assistant.
So check first if it is working as expected. If yes - just use it ( if the above answers were helpful, you may add me reputation by clicking a star in lower left corner of my post(s)).
If not - follow rule 4 of our https://www.excelforum.com/forum-rul...rum-rules.html and start a new thread. It will be a good idea to give a link to this thread in a new one, and of course to attach sample workbook.
Last edited by Kaper; 01-04-2020 at 12:44 AM.
Hi Kaper, that would generate same results as my suggestion albeit yours caters for possibility of there being no "break" which mine did not -- oops.
Adapted the below per above - I think this would be a little more efficient but tbh given they're both volatile (c/o TODAY) there's unlikely to be any notable difference {given sample size}.
Formula:C2: =IF(INDEX($D2:$IV2,$A$24)="",0,TODAY()-IFERROR(LOOKUP(2,1/($D2:INDEX($D2:$IV2,$A$24-1)=""),$D$1:INDEX($D$1:$IV$1,$A$24-1)),$D$1-1))
A24: =MATCH(TODAY(),$D$1:$IV$1,0)
On an aside I think the results for the non-gaps should read 21 rather than 20? i.e. $D$1-1
I think you are right with 21.
-1 shall be inserted in iferror formula as you suggested
csweezy,
Please don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding
For normal conversational replies, try using the quick reply box below.
HTH
Regards, Jeff
Will do. Thanks.
I input formula, and cant get it functioning. Used contrl+shift+enter, Excel accepts formula, but doesn't work. Also, cell A24 is used for employee information. I have attached full workbook. Red tab "Late-Consecutive Days" contains worksheet sample represented.
Appreciate any clarifications. Thank you
No attachment visible
BTW: have you tried my previous attachment.
Probably with the the minor correction of -1 suggested few posts below post with attachment?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks