How to add time to working days with its working hours, excluding weekend,holidays and no work hours

[Anonymous]

Hi everyone, this is my first post

i'm currently working on Power Bi for some simple reports that i need, im manipulating dates.

the problem is: i have a Initial date, but from that date i need to sum a time that can be minutes to a lots of hours, and the work hours of one day is 7:30 A.M to 17:30 P.M. The weekend and holidays must be validated also.

i read some post in community.powerbi , with the calendar + holidays table, but i can solve my problem at all, ths is the post i read https://community.powerbi.com/t5/Desktop/Add-working-days-to-a-date/td-p/146945

An example may be: 

InitialDate                            WorkHours        FinalDate
12/4/2018 17:20:00 P.M       7:50:00              12/5/2018 15:10:00 P.M

then, i have this problem, can you help me with this? thanks in advance

[Anonymous · Accepted solution]

Hi @Anonymous,

You can try to use following calculate column formula to calculate end date based on 'initial date' and 'work hours': 

EndDate =
VAR _todayRemain =
    TIME ( 17, 30, 00 )
        - MAX (
            TIME ( 7, 30, 00 ),
            MIN ( TIME ( 17, 30, 00 ), TIMEVALUE ( [InitialDate] ) )
        )
VAR _totalWorkhours =
    IF (
        WEEKDAY ( [InitialDate], 2 ) <= 5,
        [Work Hours] - _todayRemain,
        [Work Hours]
    )
VAR _workDays =
    IF (
        _totalWorkhours > 0,
        INT ( _totalWorkhours / 10 )
            + IF ( MOD ( _totalWorkhours, 10 ) > 0, 1, 0 ),
        0
    )
VAR _remaintime =
    _totalWorkhours
        - INT ( _totalWorkhours / 10 )
            * _totalWorkhours
VAR _holidayoffset =
    IF ( WEEKDAY ( [InitialDate], 2 ) >= 5, 7 - WEEKDAY ( [InitialDate], 2 ), 0 )
RETURN
    DATEVALUE ( [InitialDate] ) + _holidayoffset
        + _workDays
        + TIME ( 7, 30, 0 )
        + _remaintime

 

Regards,

Xiaoxin Sheng

[Anonymous]

How can I add 25 hours of working time to that? I tried using the value '25' in space [Work Hours] but didn't work. Also tried Time(25, 00, 00) but none of them made it ok.

Example: 28/07/2023 16:23:55 -> 02/08/2023 10:59:55

Any help?

[Anonymous]

hi @Anonymous  Xiaoxin

Thank you, it worked!! excelent answer!

a time ago a wasn't able to reply because of work, but now i appreciate you help!!! 😄 

[adambhappy]

What is your data source? Is it import or DirectQuery?

[Anonymous]

is and excel sheet, a column names "FechaInicial" and "TiempoHabil" is given to me to do the calculation