cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## Finding next WORKDAY with an if statement for time

Hello!  I have an IF statement in Excel that is looking at the time and then bringing in the next workday (excluding weekends and holidays).

If the Time Column (Z) is after 12:00PM return the following workday at 8:00AM

Here is the formula I am using in Excel.  Any help would be appreciated.

1 ACCEPTED SOLUTION
Community Support

Thanks for the reply from @Greg_Deckler , please allow me to provide another insight:

Hi  @SteffanieJ ,

Here are the steps you can follow：

First create a table of dates and holidays to get the weekday dates

1. Create calculated table.

``````Date =
var _table=
CALENDAR(
DATE(2022,1,1),DATE(2022,12,31))
return
_table,"Day",WEEKDAY([Date],2))``````

2. Enter data – holiday table.

3. Create calculated column.

``````Sales Order Create  Date & Time =
VAR _time =
TIME ( 12, 0, 0 )
VAR _nexttime =
TIME ( 8, 0, 0 )
VAR _test =
IF (
[Sales Order Create Time] > _time,
[Sales Order Create Date] + 1,
BLANK ()
)
VAR _datecolumn =
SELECTCOLUMNS (
FILTER ( ALL ( 'Date' ), 'Date'[Day] IN { 6, 7 } ),
"weekday", [Date]
)
VAR _holidaycolumn =
SELECTCOLUMNS ( ALL ( 'Holiday_Table' ), "holiday", 'Holiday_Table'[Date] )
VAR _nottable =
FILTER (
ALL ( 'Date' ),
NOT ( 'Date'[Day] )
IN { 6, 7 }
&& NOT ( 'Date'[Date] ) IN _holidaycolumn
)
RETURN
IF (
_test = BLANK (),
[Sales Order Create Date] + [Sales Order Create Time],
IF (
_test
IN _datecolumn
|| _test IN _holidaycolumn,
MINX (
FILTER ( _nottable, [Date] > EARLIER ( 'Table'[Sales Order Create Date] ) + 1 ),
[Date]
) + _nexttime,
[Sales Order Create Date] + 1 + [Sales Order Create Time]
)
)
``````

4. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

3 REPLIES 3
Community Support

Thanks for the reply from @Greg_Deckler , please allow me to provide another insight:

Hi  @SteffanieJ ,

Here are the steps you can follow：

First create a table of dates and holidays to get the weekday dates

1. Create calculated table.

``````Date =
var _table=
CALENDAR(
DATE(2022,1,1),DATE(2022,12,31))
return
_table,"Day",WEEKDAY([Date],2))``````

2. Enter data – holiday table.

3. Create calculated column.

``````Sales Order Create  Date & Time =
VAR _time =
TIME ( 12, 0, 0 )
VAR _nexttime =
TIME ( 8, 0, 0 )
VAR _test =
IF (
[Sales Order Create Time] > _time,
[Sales Order Create Date] + 1,
BLANK ()
)
VAR _datecolumn =
SELECTCOLUMNS (
FILTER ( ALL ( 'Date' ), 'Date'[Day] IN { 6, 7 } ),
"weekday", [Date]
)
VAR _holidaycolumn =
SELECTCOLUMNS ( ALL ( 'Holiday_Table' ), "holiday", 'Holiday_Table'[Date] )
VAR _nottable =
FILTER (
ALL ( 'Date' ),
NOT ( 'Date'[Day] )
IN { 6, 7 }
&& NOT ( 'Date'[Date] ) IN _holidaycolumn
)
RETURN
IF (
_test = BLANK (),
[Sales Order Create Date] + [Sales Order Create Time],
IF (
_test
IN _datecolumn
|| _test IN _holidaycolumn,
MINX (
FILTER ( _nottable, [Date] > EARLIER ( 'Table'[Sales Order Create Date] ) + 1 ),
[Date]
) + _nexttime,
[Sales Order Create Date] + 1 + [Sales Order Create Time]
)
)
``````

4. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Regular Visitor

Thank you so much!  This worked perfectly.

Super User

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...