Auto Populate Dates and its associated description from a given Year

[ismailshajji]

Hello guys, I think I am on my final legs of my journey in modifying the timesheet and Leave Record Sheet.

I am trying to Auto populate the Bank Holiday description and the Date from the year given in cell J2 the Holiday Period is April till March the Bank holiday data is given in sheet UK Bank Hols.

Regards,

[BlindAlley]

Ok, I'm not really seeing what you are trying to do here.

Using this updated template that has an automated UK Holiday list in it can you highlight the cells and the expected end result you are expecting.

[Pete_UK]

You need to be more careful with your descriptions in column J of the Leave record sheet if you want to get an exact match. For example, it should be Early May Bank Holiday as that is what you have used in the UK Bank Hols sheet. Similarly, New Year’s Day should have the apostrophe.

Also, many of your dates are wrong - they return a Tuesday date for most of the Monday Bank Holidays. I have a formula, but for some reason it is not returning the correct value for Boxing Day, so I shall investigate why before posting it.

Hope this helps.

Pete

[ismailshajji]

Thanks Pete and Blindalley, our trust financial year is from April till March as such in the Leave Record Sheet we want to mention all the bank holidays in the period April till March with their description. At present we are manually updating these holidays every year i was planning to auto mate it in a way that when an employee enters an year in column J cell 2 for example the range J4:K12 Auto populates the bank holiday dates along with its description.

[Pete_UK]

You can use this array* formula in K5 of the Leave Record sheet:

=INDEX('UK Bank Hols'!$D$2:$D$737,MATCH(1,('UK Bank Hols'!$D$2:$D$737 > =DATE($J$2,4,1))*('UK Bank Hols'!$D$2:$D$737 < DATE($J$2+1,4,1)*('UK Bank Hols'!$E$2:$E$737=J4)),0))

(note that I have had to put spaces around the < and > , as the Firewall didn't like them when I tried to post just now.

*An array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE), instead of the usual Enter.

Format the cell using a Custom Format of dddd dd/mm/yyyy, then copy the formula down.

Note that Easter can sometimes occur before 1st April in a particular year, so your date range might include two Easter and Good Friday holidays (which the formula will not account for, as it will return the first one in the range), and there might be some data ranges where there are no Easter holidays included.

In addition to cleaning up the titles in column J that I mentioned before, I noticed that you have some entries in the UK Bank Hols sheet with descriptions including phrases like "substitute day", and these will also need to be cleaned up.

Hope this helps.

Pete

[BlindAlley]

The template I uploaded has a new tab with perpetual Bank Holiday Dates and a named range to use in any formula that you choose - like Pete's INDEX above to look up the date.

[Pete_UK]

I see from Post #4 that you want to generate the descriptions in column J as well as the dates in column K, so here's a different take on it.

First, sort columns D and E in the UK Bank Hols sheet using column D as the sort field (Oldest to Newest), and put this formula in cell F2:

=IF(AND(D2 > =DATE('Leave Record Sheet'!$J$2,4,1),D2 < DATE('Leave Record Sheet'!$J$2+1,4,1)),MAX(F$1:F1)+1,"-")

Copy this down to the bottom of your list by double-clicking the Fill handle (the small black square in the bottom right corner of the cursor, with F2 selected).

Then you can use these two formulae in the Leave sheet in the cells stated:

J4: =IFERROR(INDEX('UK Bank Hols'!$E:$E,MATCH(ROWS($1:1),'UK Bank Hols'!$F:$F,0)),"")

K4: =IFERROR(INDEX('UK Bank Hols'!$D:$D,MATCH(ROWS($1:1),'UK Bank Hols'!$F:$F,0)),"")

Format K4 using a Custom Format of:

dddd, dd/mm/yyyy

then copy both formulae down to row 14 to pick up all the holidays in that date period. No need to change your descriptions with this one, although you can see the inaccuracies in the dates. Note also that Easter occurs twice in this date period.

Note that there are still some years when extra Bank Holidays are added (e.g. Queen's Jubilee), so you will need to manually insert these into the list on the other sheet.

You can also change the formula in J3 to this:

="Bank Holidays April "&$J$2&"-April "&$J$2+1

and in A5 to this:

="RECORD OF LEAVE "&$J$2&" - "&$J$2+1

so that they respond to the year in J2 - there is no need for anything in K2.

I've left the other formula in column L, for comparison.

Hope this helps.

Pete

[ismailshajji]

Thank you so much Pete and Blindally sorry for the late reply, been busy with the kids last night after work didn't get a chance to sit on computer I will look into this later tonight and let you the outcome.

[ismailshajji]

Pete Thank you so much for your help. It worked i corrected my bank holiday sheet to reflect correct dates and changed your code to check from 1st march 19 to 31 march 20