# 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.

#### 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

• 16.7 KB Views: 5
• 40.9 KB Views: 4
Last edited:

#### Peter Bartholomew

##### Well-Known Member
I use array formulas so my solution was
= (calendar>=checkIn) * (calendar<checkout)

#### Attachments

• 17.7 KB Views: 2

#### Hany ali

##### Active Member
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

• 17.7 KB Views: 2

#### Peter Bartholomew

##### Well-Known Member
Then you need to look up the check-in/out dates by name
Code:
``````=  (calendar >= XLOOKUP(name, Table1[Name], checkIn))
* (calendar < XLOOKUP(name, Table1[Name], checkout))``````

#### Attachments

• 17.8 KB Views: 7

#### Hany ali

##### Active Member
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

• 49.5 KB Views: 4

#### bosco_yip

##### Excel Ninja
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,""))

#### Hany ali

##### Active Member
Thank you very much..excellent, well done and this is what is really needed

Last edited by a moderator:

#### Peter Bartholomew

##### Well-Known Member
@bosco_yip
May I add my thanks, the OP and I were not going to see eye to eye over this!

#### Hany ali

##### Active Member
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

#### Attachments

• 17.2 KB Views: 2

#### Peter Bartholomew

##### Well-Known Member
@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!

#### Hany ali

##### Active Member
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

#### Peter Bartholomew

##### Well-Known Member
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.

#### bosco_yip

##### Excel Ninja
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

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.

bosco_yip

#### Hany ali

##### Active Member
Exactly sir,,, I hope to get the appropriate answer to my request and thank you very much

#### bosco_yip

##### Excel Ninja
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
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,""))

#### Attachments

• 15.9 KB Views: 5

#### Peter Bartholomew

##### Well-Known Member
@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!

#### Hany ali

##### Active Member
Thank you very much..excellent, well done and this is what is really needed

#### Hany ali

##### Active Member
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

• 18 KB Views: 3
• 24.4 KB Views: 3
• 14.9 KB Views: 3

#### bosco_yip

##### Excel Ninja
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,""))``
@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:

#### Excel Wizard

##### Active Member
K2 should be Le Jardain

=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

• 19.3 KB Views: 7

#### Hany ali

##### Active Member
sorry my dear
bosco_yip
I think it is for the same request
Excel Wizard
Thank you very much..excellent, well done and this is what is really needed

Status
Not open for further replies.