Peak days for leave taken

Hi all,

I have a month and a year in cell I1, i have a row with days of the week (Mon - Sun) for 31 days in E2:AI2, i have a row witch is numbered from 1-31 (E3:AI3) and finally i have a row (E4:AI4)where i enter the letter "S" for sick leave. The month (I1) automatically populates the row for mon - sun (E2:AI2). How can i get the peak day of the week (mon - sun) that leave was taken? I.o.w if a person likes to take sick leave on mondays, it must show me the peak day (mon) in cell AQ4. It must look in all the days leave was taken and give me the peak day for that leave witch will me monday.

Thank you in advance for your help.

Upload example workbook.

3 - Leave.zipHere you go.

Please don't update the links.

I'm not sure what you looking for...

This will return last day with "s".

=LOOKUP(2, 1/(LEAVE!$E4:$AI4="s"), LEAVE!$E$2:$AI$2)

But I'm not sure what you mean by peak day.

I someone likes to take sick leave on mondays more than other days of the week, it must return the value Mon. I want to know for witch day of the week the person took the most sick (S) leave. Does this make sence??

Here, try this:

Please Login or Register  to view this content.

comfirmed with ctrl+shift+enter

Edit: in case of few same days this will return first one

Almost there. If there is 3 "S" on monday and 4 on saturday it must return the value of Sat. The day with the most S's must win and produce that specific day. If i use the above formula and put "S" on Wed, it gives me "Wed", and if i continue on and put "S" on each Sat, it still shows "Wed".

Thanx for your help so far. I'm just so tired of thinking.

I manage to make this with extra row.
I couldn't find better place than putting it in Data Sheet instead of 1's...

(also, I need to change formula in 2nd row and to delete most of the workbook to reduce size).

Thank you so much. I really needed those 1's but ill see if i can work around that. Thanx a 1000000!!

Thank you. It worked. I just copied your data sheet into my workbook and now i use both data sheets for my work.

Thanx!!