Function for detect dates between Start and End date

[miky1989]

Hello everybody,

I'm raising this topic because i need your help in order to solve a problem which i have on the following excel function:

=IF(ISNA(MATCH($A6&D$4;Info!$P:$P;0));IFNA(INDEX(MarkLeave!$G:$G;MATCH(MonthlySummary!$B6&MonthlySummary!D$4;MarkLeave!$A:$A;0));"");"BH")

I will go more in details in order to explain you what i would like to do and what i did:

I'm trying to build a vacation planner. Basically i have 3x sheets:

- "MonthlySummary" (which is a table with dates, people, type of permission requests, summary of everything)
- "MarkLeave" (which is the page where people can request the permission, i built it in combination of VBA)
- "Info" (where i have a list of people, abbreviation of permissions, available and annual amount of vacation days, bank holidays)

The sense of this vacation planner is to request the permission on "MarkLeave" and taking in consideration the type, put the icon in the table visible on "MonthlySummary" .

Everything is working, but i'm having difficulties right from the moment i started to implement Start/End Dates !!
Previously, this vacation planner was only working with standard dates (one by one) but i retained necessary also to implement something for take more consecutives days together.

I also have a kind of solution, but honestly i'm finding difficulties to implement it on the formula i wrote before.
=AND(calendarday>=startdate,calendarday<=enddate

I wrote names in the formula just for let you understand better:

calendarday = is the day of the month which you can watch at attached screenshot "ex1" (from D4 to AH4)
startdate = is visible at attached screenshot "ex2" (column D)
enddate = is visible at attached screenshot "ex2" (column E)

Just as information, ID (visible on ex2) is basically Emp ID + Start Date

I would like that days between Start Date and End Date can be populated with Abbreviation name on "MonthlySummary" table.

I already did everything, but it is gonna work only with Start Date (as mentioned before, initially i built this project only for a single date) .

Are you able to support me?
Thanks in advance

Edit:
For privacy reasons, i maskered names, locations and ID

[Pete_UK]

There is not much point in attaching screenshots of an Excel workbook, especially if the details can't be read. Attach the actual file instead, by following the instructions given in the yellow banner at the top of the screen.

Also, try to make your narrative less verbose - some contributors will give up before reading to the end.

Pete

[miky1989]

Hi Pete,

I have been provided to upload my project under the main message (i just edited it little bit in order to change all informations in terms of privacy) .

Hope that now everything is clear.

Ah, another point i wanted to ask:
Speaking about VBA form ("Insert" button), i would like that date format (as tips) are visible directly from the beginning and not only when i'm going to switch between different text boxs . Do you know how to do it ?

Thanks a lot for your help !

[JeteMc]

There are no entries on the Mark Leave sheet nor corresponding expected results on the Monthly Summary sheet which we could use to see if the results of our proposed formulas are correct.

[miky1989]

I deleted everything on purpose !
You can simply add a new permission clicking on "Insert" button .

I modified the file creating and added a couple of example. Please download it again

[JeteMc]

Paste the following into cell D6 on the Monthly Summary sheet:

=IF(ISNA(MATCH($A6&D$4,Info!$P$2:$P$91,0)),IFERROR(INDEX(MarkLeave!$G$2:$G$40,AGGREGATE(15,6,(ROW(MarkLeave!$G$2:$G$40)-ROW(MarkLeave!$G$1))/(MarkLeave!$B$2:$B$40=$B6)/(MarkLeave!$D$2:$D$40<=D$4)/(MarkLeave!$E$2:$E$40>=D$4),1)),""),"BH")

After activating (Ctrl + Enter) drag the fill handle over to cell AH6 and then, while D6:AH6 are still selected, drag the fill handle down to cell AH17.
Let us know if you have any questions.

[miky1989]

That's really perfect !!! Thanks a lot, you finally solved my problem !!
Can you please explain me in short words what you did ? I would really appreciate that, i will learn and remember for next time !!

Ah, i also wanted to ask you:
Do you know how to exclude weekends from the permissions? (Ex. if icons are going to appear on the grey part of MonthlySummary table, they don't have to be calculated and counted, same for #Days on MarkLeave.

Take in consideration that some people from the list is living in other countries where the weekend is on Friday and Saturday instead of Saturday and Sunday !

Thank you again really much !!!

[JeteMc]

The formula is basically like the one in the first post with the exception of: AGGREGATE(15,6,(ROW(MarkLeave!$G$2:$G$40)-ROW(MarkLeave!$G$1))/(MarkLeave!$B$2:$B$40=$B6)/(MarkLeave!$D$2:$D$40<=D$4)/(MarkLeave!$E$2:$E$40>=D$4),1))
AGGREGATE(15,6 is pretty much like the SMALL function but it doesn't require array entry
(ROW(MarkLeave!$G$2:$G$40)-ROW(MarkLeave!$G$1)) Produces an array of values from 1:39
(MarkLeave!$B$2:$B$40=$B6) finds rows in which the Emp ID on the Mark Leave sheet match those on the Monthly Summary sheet
(MarkLeave!$D$2:$D$40<=D$4) finds rows in which the start dates on the Mark Leave sheet are less than/equal to the date on row 4 of the Monthly Summary sheet.
(MarkLeave!$E$2:$E$40>=D$4) finds rows in which the ends date on the Mark Leave sheet are greater than/equal to the date on row 4 of the Monthly Summary sheet.
the 1 at the end of the segment finds the smallest row where the above conditions are present.
As for weekends I suggest.
Conditional formatting both font and fill to gray
Put the weekend codes (1 for Sat. & Sun., 7 for Fri. & Sat.) in column AP
Use the following formula to populate the Vacation column (AI): =SUMPRODUCT(($D6:$AH6=AI$5)*(((AP6=1)*(WEEKDAY($D$4:$AH$4,2)<=5))+((AP6=7)*(WEEKDAY($D$4:$AH$4)<=5))))
Note that for the European versions of Excel I believe that the commas will need to be changed to semicolons.
Let us know if you have any questions.

[miky1989]

Thanks a lot JateMc !!

I just wanted to add a point about formula with "Aggregate" function you written yesterday :
I realized is it taking in consideration G2:G40 of MarkLeave, but consider that even more rows could be populated, not only that range.

If i would try to change for example the formula for let take in consideration the whole column, it is not going to work anymore (showing incorrect info).
What could be done for fix it ?

[JeteMc]

One solution would be to change the number 40 to the number of the last row, 1048576 for the 2019 version, so that the formula for cell D6 will be:

=IF(ISNA(MATCH($A6&D$4,Info!$P$2:$P$91,0)),IFERROR(INDEX(MarkLeave!$G$2:$G$1048576,AGGREGATE(15,6,(ROW(MarkLeave!$G$2:$G$1048576)-ROW(MarkLeave!$G$1))/(MarkLeave!$B$2:$B$1048576=$B6)/(MarkLeave!$D$2:$D$1048576<=D$4)/(MarkLeave!$E$2:$E$1048576>=D$4),1)),""),"BH")

However, this will probably produce a significant lag in calculations.
Another solution would be to convert the data range on the Mark Leave sheet into a table only containing rows that are currently filled. When a new row is added to the table the formula will update.
If you choose this option then before adding another row to the table, select cell D6 and then paste this formula into the formula bar:

=IF(ISNA(MATCH($A6&D$4,Info!$P$2:$P$91,0)),IFERROR(INDEX(MarkLeave!$G$2:$G$4,AGGREGATE(15,6,(ROW(MarkLeave!$G$2:$G$4)-ROW(MarkLeave!$G$1))/(MarkLeave!$B$2:$B$4=$B6)/(MarkLeave!$D$2:$D$4<=D$4)/(MarkLeave!$E$2:$E$4>=D$4),1)),""),"BH")

Note that Gino was added to the table after the formula was changed.
Let us know if you have any questions.

[miky1989]

That's really nice, i like it a lot !

I just didn't exactly understand what was done for not considering the weekends.

I mean, using the formula you suggested previously:

=IF(ISNA(MATCH($A6&D$4,Info!$P$2:$P$91,0)),IFERROR(INDEX(MarkLeave!$G$2:$G$4,AGGREGATE(15,6,(ROW(MarkLeave!$G$2:$G$4)-ROW(MarkLeave!$G$1))/(MarkLeave!$B$2:$B$4=$B6)/(MarkLeave!$D$2:$D$4<=D$4)/(MarkLeave!$E$2:$E$4>=D$4),1)),""),"BH")

Seems working fine but on weekends is still appearing permission's icon if the permission was requested including the weekend .

However, how you did on last file attached, seems fine, but there's 3x grey columns instead of 2x from Stefano to Enzo .

Please try to help me to understand, in order to let me be able to adapt by myself !

Thanks a lot of your help !

[JeteMc]

Not sure how the conditional formatting keeps changing, however:
The rule for greying out the Friday, Saturday weekends is: =AND($AP6=7,WEEKDAY(D$4,1)>=6)
The rule for greying out the Saturday, Sunday weekends is: =AND($AP6=1,WEEKDAY(D$4,2)>=6)
May be easier to show how this works than try to explain. See rows 22:23 for weekday numbers.
Let us know if you have any questions.

[miky1989]

That's nice, but i'm still have some problem:

- First of all, seems weird, but even if i'm copying your formula in D6 (MonthlySummary) and paste that on my own project in same position D6 + CTRL and Enter adapting that over AH6 and down to AH17 , i'm still viewing symbol of permission on the weekends.
Honesty i even didn't understand well the meaining of this formula (parts which i'm refering to, are highlighted:

=IF(ISNA(MATCH($A10&M$4;Info!$P$2:$P$103;0));IFERROR(INDEX(MarkLeave!$G$2:$G$4;AGGREGATE(15;6;(ROW(MarkLeave!$G$2:$G$4)-ROW(MarkLeave!$G$1))/(MarkLeave!$B$2:$B$4=$B10)/(MarkLeave!$D$2:$D$4<=M$4)/(MarkLeave!$E$2:$E$4>=M$4);1));"");"BH")

In addition to that, i realized that "Available Vacation" (MonthlySummary) is not giving the right result and i even understood the problem:

Basically, on "Used Vacation Days" (Info) i chose only criteria of Name, so, the system is going to sum all results in F:F (MarkLeave) when there are names which are matching with B:B (Info) .
I tried to add some other criteria on Sumif formula:

=SUM(SUMIFS(MarkLeave!F:F;MarkLeave!C:C;Info!B2;MarkLeave!G:G;Info!$G$2:$G$3))

I pressed CTRL ++ SHIFT + Enter for the additional brachet, otherwise it couldn't work on Excel's versions which are different than 365 .

The idea is to sum values on F:F (MarkLeave) if there's a permission with "H" or "V" .
It is working, but for some reason, it is taking in consideration only "V" and not "H" . Do you know why?

Thanks a lot and sorry for stressed you too much !

[JeteMc]

As to the symbols for permissions showing on weekends are the conditional formatting rules for weekends using the same grey for font and fill as mentioned in post #8?
As to understanding the formula, I attempted to explain in words what it does in post #8. Perhaps selecting a cell containing the formula and then watching it work by utilizing the Evaluate Formula feature on the Formulas tab will help.
As to the formula on the Info sheet it seems that it would be simpler to write it as =SUM(SUMIFS(MarkLeave!F:F;MarkLeave!C:C;Info!B2;MarkLeave!G:G;Info!$G$2),SUMIFS(MarkLeave!F:F;MarkLeave!C:C;Info!B2;MarkLeave!G:G;Info!$G$3))
Also, I noticed that on the Mark Leave sheet the formula for column F is: =IF(G2="H";"0,5";IF(DATEDIF(D2,E2,"d")=0;"1";DATEDIF(D2,E2,"d")+1)) such that "0,5" and "1" are text which the SUMIFS function will ignore.
I suggest changing that to: =IF(G2="H";0,5;IF(DATEDIF(D2,E2,"d")=0;1;DATEDIF(D2,E2,"d")+1)) and actually =IF(G2="H";0,5;DATEDIF(D2,E2,"d")+1))
Let us know if you have any questions.

[miky1989]

- Regarding the symbols for permission on weekends, i don't mean the color of the box, but the name of the symbol (ex. "S", "V", etc) which is appearing on weekends . Honestly i didn't understand how you did, looking on conditional formatting (selecting D6:AH18) seems everything written in same way of my file :/

- About "datedif" formula, i already realized it and corrected this problem yesterday. From one side seemed everything solved, but then, i realized that it was counting everything not only "V" and "H" .
Writing formula you suggested, nothing changed, but i'm getting the same value (excel is not considering "H" , only "V")

=SUM(SUMIFS(MarkLeave!F:F;MarkLeave!C:C;Info!B2;MarkLeave!G:G;Info!$G$2),SUMIFS(MarkLeave!F:F;MarkLeave!C:C;Info!B2;MarkLeave!G:G;Info!$G$3))

[AliGW]

Administrative Note:

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.

[JeteMc]

Please include a file that illustrates the issues mentioned in post #15 along with your next post.

[miky1989]

Sure !
I've attached two files:

"1" is the file attached previously from you. In the table it seems working well, abbreviations are not appearing if there's a weekend

"2" is my project with some edit (still, modified a bit for privacy reasons). Here, abbreviations are appearing if there's a weekend (that's what i need to fix and understand how to do it) but not counted on the grand total (and that's right, i'm fine with that) .

On both files, going at Info sheet, column "D" , excel is not calculatin the amount of "H" (half day) . Thinking that it could depends from "," , i tried some different formula.. but still.. the problem persist.

Hope you could be able to help me even on this :/

[JeteMc]

When I opened 2.xlsm I found:
As to the conditional formatting the fill color was set to light gray but the font was set to Automatic.
Select cell D6
Select Conditional Formatting and then Manage Rules
Select the first rule and then Edit Rule
Select Edit Rule and then the Format button
From the Font tab select the drop down under Color
Select the second square in the third column (if you hover the cursor over that square it should read Light Gray...)
Repeat the above for the second rule.
As to column D on the Info sheet, it seems that the formula currently there is picking up the half day for Andrea. What doesn't seem to be working is the formula for vacation days as Andrea only used five (since 3 of the 8 were weekend days).
To correct this, I changed the formula in column F on the MarkLeave sheet to read: =IF(G2="H",0.5,NETWORKDAYS.INTL(D2,E2,INDEX(MonthlySummary!AP$6:AP$18,MATCH(C2,MonthlySummary!C$6:C$18,0))))
This last change will probably require a bit of testing so let us know if you run into any problems or have any questions.

[miky1989]

Thank you really much Jete, i really appreciated what you did for me.

By the way, for the calculation of "Half Days", i created another column and with sumifs function i collected half days requests .
Making the difference between vacations and half days on column "AS" (Monthly Summary), i'm able to get the correct available days number.

There's only one thing more to fix:
In case of holydays, sundays or saturdays, the system doesn't have to count half days or vacations days . There could be a way to implement weekday function even on "F" (MarkLeave) ?

After this, i think everything we did, will be enought !

Thanks again !

[JeteMc]

I added a column (M) to display the Team using: =INDEX(Info!A$2:A$14,MATCH([@[Emp Name]],Info!B$2:B$14,0))
The formula in column F is:

=NETWORKDAYS.INTL(D2,E2,INDEX(MonthlySummary!AP$6:AP$18,MATCH(C2,MonthlySummary!C$6:C$18,0)),OFFSET(Info!O$1,MATCH([@Team],Info!N$2:N$103,0),0,COUNTIFS(Info!N$2:N$103,[@Team])))*IF(G2="H",0.5,1)

Let us know if you have any questions.

[miky1989]

Sorry for didn't answer you JateMC, but i wasn't online on last days.

I was checking the project, and i figured out another issue:
Selecting the month on "MonthlySummary" Sheet, Gran Total table is not to change with correct data (#Value! is appearing) .

(Check also I3 and J3 for a better understanding)

It seems i'm getting this error when i'm going to switch to months which don't have 31 days

Could you please check it? I'm really not understanding the reason as before it was working. Problably it is because of "Weekday" function implemented in the formula AI to AN of MontlySummary :/

[JeteMc]

Modify the formula in AI6:AN18 to read:

=SUMPRODUCT(($D6:INDEX($D6:$AH6,AGGREGATE(14,6,(COLUMN($D$4:$AH$4)-COLUMN($C$4))/($D$4:$AH$4<>""),1))=AI$5)*(((AP6=1)*(WEEKDAY($D$4:INDEX($D$4:$AH$4,AGGREGATE(14,6,(COLUMN($D$4:$AH$4)-COLUMN($C$4))/($D$4:$AH$4<>""),1)),2)<=5))+((AP6=7)*(WEEKDAY($D$4:INDEX($D$4:$AH$4,AGGREGATE(14,6,(COLUMN($D$4:$AH$4)-COLUMN($C$4))/($D$4:$AH$4<>""),1)))<=5))))

Let us know if you have any questions.