5 Replies Latest reply on Feb 12, 2019 9:22 PM by Kielty Walsh

# Adjusting Dates for Holiday's- Starting date at following day opening hours.

Hello All,

I've seen this issue talked about on multiple forums, however have not been able to crack how to solve for my case scenario.. maybe I have just stared at this sheet for too long, however desperate for new set of eyes!

In short what I am trying to do is count the amount of minutes in between two dates that occur during working hours. I was able to get the calculations to solve for this, and account for weekends, however am running into a wall when trying to adjust for public holidays.

Right now the data is linked to a sheet that identifies which dates are public holidays. What i need is a calculation that says if this date is a public holiday, then create a new start date that is the following day at 8am. The date add field has not been helping me, as I need to make sure the hour is 8am on the data piece.

Let me know if you have any thoughts of what I could try... I have attached a sample worksheet.

• ###### 1. Re: Adjusting Dates for Holiday's- Starting date at following day opening hours.

Hi, Kielty

Please find my calculation for new start date time below

Probably you can use this to replace your start date in your alternate calculation

let me know if it works or not.

ZZ

• ###### 2. Re: Adjusting Dates for Holiday's- Starting date at following day opening hours.

Hi Zhouyi,

Thanks for responding. When I entered in that same logic onto my sheet, it automatically updated the 2019 field to ATTR?

Any idea why this would happen?

• ###### 3. Re: Adjusting Dates for Holiday's- Starting date at following day opening hours.

Hi, Kielty

my bad, forgot to mention that you need join your holiday data to your raw data as shown below first.

Let me know if you have question.

ZZ

• ###### 4. Re: Adjusting Dates for Holiday's- Starting date at following day opening hours.

Ok that is where my issue is stemming from then. The workbook I am actually working off of, is pulling the date diff data from a tableau server. Just for the example did I put it the dates into excel has the rest of document contains confidential data. So I do not have that option to join the tables with a left join. Instead my sheets are just joined with relationships:

• ###### 5. Re: Adjusting Dates for Holiday's- Starting date at following day opening hours.

I was able to solve this using the following:

IF DATETRUNC('day',[AUS Time Start])=#01/01/2019#

OR DATETRUNC('day',[AUS Time Start])=#28/01/2019#

OR DATETRUNC('day',[AUS Time Start])=#11/03/2019#

OR DATETRUNC('day',[AUS Time Start])=#19/04/2019#

OR DATETRUNC('day',[AUS Time Start])=#22/04/2019#

OR DATETRUNC('day',[AUS Time Start])=#25/04/2019#

OR DATETRUNC('day',[AUS Time Start])=#10/06/2019#

OR DATETRUNC('day',[AUS Time Start])=#05/11/2019#

OR DATETRUNC('day',[AUS Time Start])=#25/12/2019#

OR DATETRUNC('day',[AUS Time Start])=#26/12/2019#