• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Adjust the equation for dividing the number of nights for each employee name based on the beginning of the date

Status
Not open for further replies.
H

Hany ali

Active Member
Hello My Dear,I Want Your Help to Adjust this equation for dividing the number of nights for each employee name based on the beginning of the date
for example : if Ahmed Khalifa in sheet1 start his Stay from 08/05/2022 till 13/05/2022 ,, it mean he will stay 5 Night ..i Want to Divid this Number Of Nights From 08/05/2022 in Summary Sheet ..To be in each cell number 1 until the end of the period, which will be on 12/05/2022
Code: 
=IF(AND(B2<>"",SUM($B2:B$3)<=VLOOKUP($A2,Sheet1!$D$2:$G$170,4,0)),MROUND(VLOOKUP($A2,Sheet1!$D$2:$G$170,4,0)/VLOOKUP($A2,Sheet1!$D$2:$G$170,4,0),0),IF(VLOOKUP($A2,Sheet1!$D$2:$G$170,2,0)=C$1,MROUND(VLOOKUP($A2,Sheet1!$D$2:$G$170,4,0)/VLOOKUP($A2,Sheet1!$D$2:$G$170,4,0),0),""))
If Possible Function Shorter Than This One
thanks with my Best Regards
 

Attachments

Last edited:
thanks Alot Mr Peter ,But I want the equation to be also related to the name of the employee ,I mean it must be divid the Number in Summary Sheet only when I Enter Employee Name... Not Like Photo
 

Attachments

  • Untitled.png
    Untitled.png
    17.7 KB · Views: 2
Good..but excuse me sir. Is it possible to do this with Normal equations? I always and never face a problem when applying this kind of solution to me, as you can see
Because This is not the original file, so I can apply it to the original file. Thank you
 

Attachments

  • Untitled.png
    Untitled.png
    49.5 KB · Views: 4
Try,

In B2, formula copied across right and all filled down :

=IF($A2="","",IF((VLOOKUP($A2,Sheet1!$D$2:$F$500,2,0)<=B$1)*((VLOOKUP($A2,Sheet1!$D$2:$F$500,3,0)-1)>=B$1),1,""))

79127
 
Thank you very much..excellent, well done and this is what is really needed
 
Last edited by a moderator:
@bosco_yip
What makes me think the OP won't go for my modified formula?
Code: 
= LET(binaryString,
      MAP(name, LAMBDA(n,LET(
         In,  FILTER(checkIn, Table1[Name]=n),
         Out, FILTER(checkout, Table1[Name]=n),
         CONCAT(SCAN(0,calendar,LAMBDA(occupied?,day,
            occupied? + SUM(SWITCH(day, In, 1, Out, -1, 0)))
         ))
      ))),
      d, SEQUENCE(1,COUNT(calendar)),
      VALUE(MID(binaryString,d,1))
  )
About the best thing to be said for it is that it works!
79138
 
Honestly, dear sir, I can't deal with this kind of answers ... thanks alot for Your Help ,But Please I want Normal Function..Really I Don't Know Where Must Be To Put This Code
 
In this case, I have every sympathy with you. The formula only works in Excel 365. It goes into the top-left cell of the range it populates. It assumes that 'name' has been set as a defined name applied to the first column of your output table and that your input is held in an Excel Table. This use of Names and Structured References is why you see no direct references of the kind $F$500. The formula could be simplified if I wrote it for a single output row and left it for you to copy/fill down but I think it is still too far out of your comfort zone.
 
Peter Bartholomew said:
In this case, I have every sympathy with you. The formula only works in Excel 365. It goes into the top-left cell of the range it populates. It assumes that 'name' has been set as a defined name applied to the first column of your output table and that your input is held in an Excel Table. This use of Names and Structured References is why you see no direct references of the kind $F$500. The formula could be simplified if I wrote it for a single output row and left it for you to copy/fill down but I think it is still too far out of your comfort zone.
Click to expand...
@Peter Bartholomew

Please be noted that Hany Ali doesn't accept to your answer, because he has Excel 2016 only,

version too old and unable to support O365 new functions.

However, I like your answer.

bosco_yip
 
Hany ali said:
mr.
bosco_yip

Can the equation be modified if the employee’s name is repeated to me more than once during the same month but different period ?
thanks alot
Click to expand...
Here is my formula solution, in using Aggregate function and can suit with your Excel 2016 version

In B2, formula copied across and down :

=IF($A2="","",IF(ISNUMBER(AGGREGATE(15,6,ROW(Sheet1!$D:$D)/(Sheet1!$D$2:$D$100=$A2)/(Sheet1!$E$2:$E$100<=B$1)/((Sheet1!$F$2:$F$100-1)>=B$1),1)),1,""))

79143
 

Attachments

@bosco_yip
Thank you for the additional information. It wasn't apparent to me what version of Excel was in play, so I posted the solutions on a 'just in case' basis. These days there seems to be about a 50-50 chance of success of finding the OP is a 365 user (that is a subjective judgement, you may have better information).

Ultimately, it may be the user base rather than the code availability that limits change in the practice of spreadsheet development, so I have continued posting 'strange' solutions simply to raise awareness. I did offer to @Hui to stop posting if the general opinion was that I spread more confusion than light. On the positive side I know that there are a handful of members who do study my posts, so I am not entirely talking to myself.

BTW, I liked your use of the AGGREGATE function; the function has useful behaviours that are not immediately apparent!
 
Hello Mr.
bosco_yip
Is it possible to add another condition to the equation, which is the name of the hotel which in cell K2 BY Which in Column B From sheet1
If the same person is repeated in the same time intervals, the division must be 2 and not 1, if it is repeated twice, but if it is repeated more than that, the distribution must be in the number of repetitions
Code: 
=IF($A2="","",IF(ISNUMBER(AGGREGATE(15,6,ROW(Sheet1!$D:$D)/(Sheet1!$D$2:$D$100=$A2)/(Sheet1!$E$2:$E$100<=B$1)/((Sheet1!$F$2:$F$100-1)>=B$1),1)),1,""))
 

Attachments

Hany ali said:
Hello Mr.
bosco_yip
Is it possible to add another condition to the equation, which is the name of the hotel which in cell K2 BY Which in Column B From sheet1
If the same person is repeated in the same time intervals, the division must be 2 and not 1, if it is repeated twice, but if it is repeated more than that, the distribution must be in the number of repetitions
Code: 
=IF($A2="","",IF(ISNUMBER(AGGREGATE(15,6,ROW(Sheet1!$D:$D)/(Sheet1!$D$2:$D$100=$A2)/(Sheet1!$E$2:$E$100<=B$1)/((Sheet1!$F$2:$F$100-1)>=B$1),1)),1,""))
Click to expand...
@Hany ali

The above is a new count unique question, of which different from your post 1 question >> all result to be 1 (which is a conditional formatting rule formula question and will produce a colored bar chart design).

So, post a new post for your new question with your attachment

Thank you

Regards

bosco_yip
 
Last edited:
K2 should be Le Jardain

Please try
=COUNTIFS(Sheet1!$B$2:$B$99,$K$2,Sheet1!$D$2:$D$99,$A4,Sheet1!$E$2:$E$99,"<="&B$3,Sheet1!$F$2:$F$99,">"&B$3)

Custom format # to hide Zero

or
=IFERROR(--TEXT(COUNTIFS(Sheet1!$B$2:$B$99,$K$2,Sheet1!$D$2:$D$99,$A4,Sheet1!$E$2:$E$99,"<="&B$3,Sheet1!$F$2:$F$99,">"&B$3),"#"),"")
 

Attachments

Status
Not open for further replies.
Back
Top