Jump to content


Photo

List Holidays that are also workdays

edate workday eom min

  • Please log in to reply
7 replies to this topic

#1 EJS

EJS

    Newbie

  • Members
  • Pip
  • 5 posts

Posted 22 September 2017 - 12:35 AM

I created a spreadsheet using (EDATE, WORKDAY, EOM, MIN) that solves for...

A Business Day Convention whereby payment days that fall on a Holiday or Saturday or a Sunday roll forward to the next TARGET Business Day, unless that day falls in the next calendar month, in which case the payment day rolls backward to the immediately preceding TARGET Business Day.

However now I want to "list" the days that are holidays that fall on a WORKDAY.  For instance, if I am in the US then Dec 25 is a holiday. I want to "list" that result. However, if I am pointing the column that is for the UK, then Dec 25 and 26 are holidays, I want to "list" both days.

I created a small version of a UK calendar that looks at the third month after Sept 25...
 
my calendar  day                B2 = 9/25/17
using EDATE in cell B3      =EDATE(B2,3)  result =  12/25/17   
 (third month from Sept)
my condensed calendar for the UK are in cells e2:e4 with dates of aug 28, dec 25, dec 26
using WORKDAY               =WORKDAY(B3-1,1,E2:E5) RESULT = 12/27/17 as Dec 25 and 26 are holidays

What formula could I use "list' in another cell the RESULT = "Dec 25" when I point to the US column, and "Dec 25, Dec 26" when looking at UK column?

Thanks!



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 866 posts

Posted 22 September 2017 - 06:44 PM

Hi EJS,

 

I'm having a hard time understanding exactly what you're trying to do. If you could attach your example workbook it would make it much easier. You can find instructions for how to attach a workbook here.

 

I've tried to recreate your workbook as described.

 

Workday.png

 

The WORKDAY function returns the date after a specified number of days, skipping weekends and the defined holidays. In this case you have asked WORKDAY to return the date 1 day after the 25th of December 2017, skipping the holidays defined in cells E2:E4. WORKDAY looks 1 day after the 25th, sees that it is a holiday and skips to the 27th.

 

I can't quite understand what you mean when you say "I want to "list" the days that are holidays that fall on a WORKDAY.". By definition, holidays are not work days? You also need to have already defined your table of holidays in order for the WORKDAY function to work, so your holidays are already listed.

 

If what you mean is that you want to define separate holiday tables for the UK and the US, you can do this very easily. Simply create two tables and then specify which table you wish to use in your WORKDAY formula.

 

If you want your formula to use a different table depending on the country, you can do this with an IF function.

 

For example:

=IF(A4="UK",WORKDAY(B4,C4,UKHolidays[Date]),WORKDAY(B4,C4,USHolidays[Date]))

 

You can see how to use the IF function in the video lesson: Expert Skills Lesson 3-5 Create an Excel IF logical function.

UKHolidays[Date] is a structured reference. You can see how these work in the video tutorial: Expert Skills Lesson 1-17 Name a table and create an automatic structured table reference.

 

This uses the UKHolidays table for the UK and the USHolidays table for anything else.

 

I have attached an example of this in action:

 

Attached File  Workday.xlsx   13.69KB   18 downloads

Workday2.png

 

As you can see, the UK record has a later end date than the US record because there is an additional holiday at the start of January for the UK.

 

I hope this is what you were looking for, but please feel free to reply if you need any more help with this, ideally attaching an example.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#3 EJS

EJS

    Newbie

  • Members
  • Pip
  • 5 posts

Posted 22 September 2017 - 10:04 PM

Thanks for your speedy response!

In cell B5 of the recreated worksheet, it "omits" the holidays from the noted calendars. Essentially, I want to "list the holidays" that occur for each country in separate cells. So in the example in the US, Dec 25 is a holiday and in UK Dec 25 and 26 are holidays.

So, in the recreated spreadsheet above, i still need B5, what I am trying to figure out is how to list the holidays, so that let say...

A7 said "US Holidays". B7 would then return "Dec 25"
A8 said "UK Holidays". B8 would then return "Dec 25, Dec 26"

So what would the formula be for B7 & B8?

Thanks!

#4 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 866 posts

Posted 22 September 2017 - 10:34 PM

Hi EJS,

 

I'm still not sure I understand, perhaps you're looking for an in-built function that returns a list of public holidays by country? If so, I'm afraid no such function exists. You will have to maintain your own lists of public holidays within Excel.

 

If you are just trying to extract them as comma-separated lists, you can do this easily with the TEXTJOIN function, although this is only available in Excel 2016 or later. It's estimated that over 80% of Excel users are now using Excel 2016, so hopefully this will work for you.

 

The procedure is almost the same as shown above, but with the addition of the TEXTJOIN function:

=IF(F3="UK",TEXTJOIN(",",TRUE,UKHolidays[DateText]),TEXTJOIN(",",TRUE,USHolidays[DateText]))

 

As you can see, an IF function is used to check which country is selected and then TEXTJOIN returns the list of dates from the appropriate table, separated by a comma.

 

I have attached an example of this in action:

 

Attached File  CSVHolidays.xlsx   12.61KB   19 downloads

CSVHolidays.png

 

You'll notice that I've added a DateText column that uses the TEXT function to convert the dates into text before they are joined together. It's necessary to do this, otherwise the date serial numbers will appear in the final result instead of the textual dates.

 

You can see more about how to use the TEXT function in the video lesson: Expert Skills Lesson 3-19 Use the TEXT function to format numerical values as strings.

For an explanation of date serial numbers, see: Expert Skills Lesson 3-7 Understand Excel date serial numbers and custom date formats.

 

I hope this is the answer you were looking for, but please feel free to reply if you still need any more help.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#5 EJS

EJS

    Newbie

  • Members
  • Pip
  • 5 posts

Posted 23 September 2017 - 12:19 AM

Thanks again for your help!

You got me to the final Result!!!
 

 

UK Holidays 25-Dec-17, 26-Dec-17 US Holidays 25-Dec-17


I used your advice and converted to Text and use this formula...

{=TEXTJOIN(", ",TRUE,IF(F4:F12>=$B$3,IF(F4:F12<=$B$6,G4:G12,""),""))}

See attached spreadsheet


BTW...I been using Excel for a long time and bought both Essential and Expert books.  The Essential for my boys and Expert for me.  I thought I breeze through the Essential Book quickly and let my sons read them.  I was amazed with how much I learned.  Right now, I am halfway through the Expert book.  I probably would have been done a few weeks back, but your lessons are so useful that every time I learn something, I go to my spreadsheets and make them a work of art!  I really can't believe how much better they are than just a month ago.  I was so thrilled, that I bought two more books and sent them to my son in college.  Thanks again!!

Please let me know if you decide to produce, Word, Word Expert, Powerpoint, I would buy in a second!  Or let me know if you recommend any books that cover those topics.  I have a solid background in them, but as I learned in your Essentials book, there are things that I still could do better.

 

Thanks!

(fyi- I will probably have a few more questions)
 

Attached Files



#6 EJS

EJS

    Newbie

  • Members
  • Pip
  • 5 posts

Posted 23 September 2017 - 03:56 PM

Ugh! This doesn't work at the office with Excel 2010

#7 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 866 posts

Posted 24 September 2017 - 04:47 PM

Hi EJS,

 

As I said, the TEXTJOIN function is only available in Excel 2016 or later. It is possible to do the same thing in earlier versions of Excel by using the CONCATENATE function or the concatenation operator (&), but requires much more complex formulas.

 

You can see more about concatenation in the video lesson: Expert Skills Lesson 3-18 Concatenate strings using the concatenation operator.

 

I have attached an example that will work with legacy versions of Excel:

 

Attached File  CSVHolidays-Legacy.xlsx   13.27KB   15 downloads

CSVHolidays-Legacy.png


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#8 EJS

EJS

    Newbie

  • Members
  • Pip
  • 5 posts

Posted 26 September 2017 - 12:00 AM

Thanks very much for all your help!






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users