Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rocky09
Solution Sage
Solution Sage

Calculating Working hours

I have this following Data, I am trying to find a way to calculating  Working hours in betwen dates excluding Weekends.

Works hours are between: Morning 9:00 AM to Evening 6:00 PM and Saturday and Sunday are holidays.

 

TicketIDACTIVITY_DATE       LASTMODIFIEDDATE    
8625628-12-2017 03:1128-12-2017 15:11
8989015-08-2017 20:2315-08-2017 21:12
11161104-10-2017 21:3010-10-2017 13:00
11151104-10-2017 02:3010-10-2017 13:00
11154204-10-2017 02:4110-10-2017 13:00
11148504-10-2017 02:4110-10-2017 13:00
21141106-10-2017 14:2510-10-2017 13:00
15089510-11-2017 12:3218-01-2018 13:29
15299608-08-2017 03:4009-08-2017 11:00

 

any help?

 

 

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

 

HI @rocky09,

 

You can try to use below calculated column formula to calculate valid working hour:

 

Work Hour = 
VAR filtered =
    FILTER (
        ADDCOLUMNS (
            CROSSJOIN (
                CALENDAR ( [ACTIVITY_DATE], [LASTMODIFIEDDATE] ),
                SELECTCOLUMNS ( GENERATESERIES ( 9, 18 ), "Hour", [Value] )
            ),
            "Day of week", WEEKDAY ( [Date], 2 )
        ),
        [Day of week] < 6
            && [TicketID] = EARLIER ( Table1[TicketID] )
    )
VAR hourcount =
    COUNTROWS (
        FILTER (
            filtered,
            (
                [Date] >= DATEVALUE ( [ACTIVITY_DATE] )
                    && [Hour]
                        > HOUR ( [ACTIVITY_DATE] ) + 1
            )
                && (
                    [Date] <= DATEVALUE ( [LASTMODIFIEDDATE] )
                        && [Hour]
                            > HOUR ( [LASTMODIFIEDDATE] ) - 1
                )
        )
    )
VAR remained =
    DATEDIFF (
        TIMEVALUE ( [ACTIVITY_DATE] ),
        TIME ( HOUR ( [ACTIVITY_DATE] ) + 1, 0, 0 ),
        MINUTE
    )
        + DATEDIFF (
            TIME ( HOUR ( [LASTMODIFIEDDATE] ) - 1, 0, 0 ),
            TIMEVALUE ( [LASTMODIFIEDDATE] ),
            MINUTE
        )
RETURN
    IF ( hourcount <> BLANK (), (hourcount*60 + remained)/60, 0 )

 2.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

34 REPLIES 34
Anonymous
Not applicable

I know this is already solved and just wanted to post this for others in the future.

https://www.villezekeviking.com/calculating-handling-time-during-office-hours/

 

This blog post explains the ways of calculating office hours using DAX and has generatic examples to copy from

ashleybaldwin
Frequent Visitor

Bring some new life to an old thread.

I was staring at this issue with no idea where to start, and then I stumble on this thread which gave me hope.

Unfortunately the examples in this thread gave wildly wrong figures and missed a couple of edge cases that may data was riddled with.

 

So with massive inspiration from this thread I have put the below together which as far as I can tell is spot on for my 300,000+ data set:

Business Hours = 

VAR BusinessStart = 8
VAR BusinessEnd = 18
VAR StartDate = DATEVALUE ( [Start] )
VAR StartHour = HOUR ( [Start] )
VAR EndDate = DATEVALUE ( [End] )
VAR EndHour = HOUR ( [End] )

//Generate a table of Dates and Hours based on the Start and End of item
VAR BusinessCalendar =
    FILTER (
        ADDCOLUMNS (
            CROSSJOIN (
                CALENDAR ( StartDate, EndDate ),
                SELECTCOLUMNS ( GENERATESERIES ( BusinessStart, BusinessEnd ), "Hour", [Value] )
            ),
            "Day of week", WEEKDAY ( [Date], 2 )
        ),
        [Day of week] < 6
    )


//Calculate the number of hours on the first day
//Also check if the start and end dates are the same and account for that, will drop lastDayHours in final calc
VAR firstDayHours = 
	IF ( StartDate <> EndDate,

		COUNTROWS (
			FILTER (
				BusinessCalendar,
				([Date] = StartDate && [HOUR] > StartHour )
				)
			),
			
		COUNTROWS (
			FILTER (
				BusinessCalendar,
				([Date] = StartDate && [HOUR] > StartHour && [HOUR] < EndHour )
				)
			)
			
		)

//Calculate hours in last day
VAR lastDayHours = 
	COUNTROWS (
		FILTER (
			BusinessCalendar,
			([Date] = EndDate && [HOUR] < EndHour )
			)
		)
		
//Add first and last hours together unless both same day
VAR combinedHours = 
	IF ( StartDate = EndDate,
		firstDayHours,
		firstDayHours + lastDayHours
	)

//Calculate the hours from the full days
VAR fullDays = 
	COUNTROWS (
		FILTER (
			BusinessCalendar,
			([Date] > StartDate && [Date] < EndDate)
		)
	)


//Calculate the minutes in the hours at each end of the date/time period
//Check if start and end hours are the same and account for that
VAR remainingMinutes = 
	IF ( StartDate <> EndDate,
		(60 - MINUTE ( [Start] )) + MINUTE ( [End] ),
		IF ( StartHour <> EndHour,
			(60 - MINUTE ( [Start] )) + MINUTE ( [End] ),
			MINUTE ( [End] ) - MINUTE ( [Start] )
		)
	)
			
	
RETURN
	( ( (combinedHours + fullDays ) * 60 ) + remainingMinutes ) / 60

NOTE - This does not account for End dates that are after the Start date, these will be calculated as negative values, this was not an issue for my data set but this could be handled with the MIN function to clamp to 0

Anonymous
Not applicable

This is great! How would I go about excluding holidays as well? 

MarcUrdang
Post Patron
Post Patron

Hi

 

Please can you advise .. I have tried using the scripts to calculate business hours ... its not even close ... I am pretty sure I am doing it correctly but clearly not 🙂 

 

Please can you help

thanks

Marc

Bruno_Camacho
New Member

Hi, I worked on this new solution, i hope it can usefull for you.

 

Any questions you can contact me.

 

Regards!

 

Tiempo Minutes = 
VAR WrongDate = IF([Fecha y hora de correo]>[Fecha y hora Primera llamada],TRUE(),FALSE())
VAR SDate_1 = MIN([Fecha y hora de correo],[Fecha y hora Primera llamada])
VAR FDate_1 = MAX([Fecha y hora de correo],[Fecha y hora Primera llamada])
VAR SDate_2 = IF(TIMEVALUE(SDate_1) < TIME(8,30,0),DATEVALUE(SDate_1)+TIME(8,30,0)
                    ,IF(TIMEVALUE(SDate_1) > TIME(17,30,0),DATEVALUE(SDate_1)+TIME(17,30,0)
                        ,SDate_1)) - (30/60/24)
VAR FDate_2 = IF(TIMEVALUE(FDate_1) < TIME(8,30,0),DATEVALUE(FDate_1)+TIME(8,30,0)
                    ,IF(TIMEVALUE(FDate_1) > TIME(17,30,0),DATEVALUE(FDate_1)+TIME(17,30,0)
                        ,FDate_1)) - (30/60/24)

VAR SDate_3 = IF(WEEKDAY(SDate_2,2)=6,DATEVALUE(SDate_2)-(WEEKDAY(SDate_2,2)-5)+(17/24),SDate_2)
VAR FDate_3 = IF(WEEKDAY(FDate_2,2)=6,DATEVALUE(FDate_2)-(WEEKDAY(FDate_2,2)-5)+(17/24),FDate_2)

VAR Calendar_1 = FILTER(ADDCOLUMNS(CROSSJOIN(CALENDAR(SDate_3,FDate_3),SELECTCOLUMNS(GENERATESERIES(8, 17),"Hour",[Value]))
                                  ,"DOW",WEEKDAY([Date],2),"Date_Time",[Date]+[Hour]/24),[DOW] < 6)
VAR Calendar_2_1 = FILTER(ADDCOLUMNS(Calendar_1,"Val_Inicio",IF([Date_Time]>=SDate_3 && [Date_Time]<=FDate_3,1,0)),[Val_Inicio] = 1)
VAR Calendar_2_2 = SELECTCOLUMNS(Calendar_2_1,"Fecha",[Date],"FechaTiempo",[Date_Time])

VAR Calendar_3 = SUMMARIZE(Calendar_2_1,[Date],"Min_DateTime",MINX(FILTER(Calendar_2_2,[Fecha] = [Date]),[FechaTiempo])
                                            ,"Max_DateTime",MAXX(FILTER(Calendar_2_2,[Fecha] = [Date]),[FechaTiempo]))
VAR Calendar_4 = ADDCOLUMNS(Calendar_3,"Date_Start",IF(DATEVALUE([Min_DateTime]) = DATEVALUE(SDate_3),MIN([Min_DateTime],SDate_3),[Min_DateTime])
                                      ,"Date_End",IF(DATEVALUE([Max_DateTime]) = DATEVALUE(FDate_3),MAX([Max_DateTime],FDate_3),[Max_DateTime]))
VAR Time = SUMX(ADDCOLUMNS(Calendar_4,"Minutes",DATEDIFF([Date_Start],[Date_End],MINUTE)),[Minutes])
VAR Minutes = IF(ISBLANK(Time),DATEDIFF(SDate_1,FDate_1,MINUTE),Time)
RETURN
IF(WrongDate,Minutes*-1,Minutes)

Example.png

 

MarcelWoodman
Regular Visitor

I know this is a bit of an older thread, but needed to implement this and found that there were some errors/inconsistencies in the accepted solution, so I thought I'd post my modified code in the event that anyone else needs it in the future:

FR_WorkHour = 

VAR Start_Date = 'SQL: Tickets'[created_at] //set the start date/time variable
VAR End_Date = if('SQL: Tickets'[first_responded_at] <> BLANK(),'SQL: Tickets'[first_responded_at],if('SQL: Tickets'[resolved_at] <> BLANK(), 'SQL: Tickets'[resolved_at],NOw())) // set the end date/time variable


/* Create a dynamic calendar from the start and end dates, and create a cross join table with the available working hours */
VAR workcal =
    FILTER (
        ADDCOLUMNS (
            CROSSJOIN (
                CALENDAR ( DATEVALUE(Start_Date), DATEVALUE(End_Date) ),
                SELECTCOLUMNS ( GENERATESERIES ( 8, 17 ), "Hour", [Value] ) // set the work hours here. If you need to include a break, change the generate series to an array of the start hours e.g. [9,10,11,13,14,15...]
            ),
            "Day of week", WEEKDAY ( [Date], 2 )
        ),
        [Day of week] < 6
    )

/* Count the number of whole hours between the datetimes */
VAR hourcount = 
    COUNTROWS(
        FILTER (workcal,
            (
            ([Date] = DATEVALUE( Start_Date )
                && 
                [Hour] > HOUR( Start_Date ))
            && 
            ([Date] = DATEVALUE( End_Date ) 
                && 
                [Hour] <= HOUR( End_Date ))
            )
            ||
            ([Date] > Start_Date  && [Date] < End_Date )
        )
    )

/* Determine how much extra time (in minutes) is outside of the whole hours (hourcount) */
VAR remained = 
    if(
        DATEVALUE(Start_Date) = DATEVALUE(End_Date) && HOUR(Start_Date) = HOUR(End_Date), 
            DATEDIFF(Start_Date,End_Date,MINUTE),
            DATEDIFF (
                TIMEVALUE ( Start_Date ),
                TIME ( HOUR ( Start_Date ) + 1, 0, 0 ),
                MINUTE
            )
            + 
            DATEDIFF (
                TIME ( HOUR ( End_Date ) - 1, 0, 0 ),
                TIMEVALUE ( End_Date ),
                MINUTE
            )
    )

VAR worktime = (hourcount*60 + remained)/60

RETURN
    worktime

Hi @MarcelWoodman ,

There is an error message "An argument of function 'TIME' has the wrong data type or the result is too large or too small."

I have changed the data type for [Start_Date] and [End_Date] to Date/Time.

Please help. Thank you.

 

 

@PBI_newuser, If I had to guess, I think it would have to be in the last function where we subtract 1 from the hour. If the time that the ticket is closed is at the midnight hour (0:00) then it could return -1, which is an invalid time.

 

Try throwing an if statment in to say if hour = 0, then 23, else -1. Something like this:

DATEDIFF (
                TIME ( IF ( HOUR ( End_Date ) = 0, 23, HOUR ( End_Date ) - 1), 0, 0 ),
                TIMEVALUE ( End_Date ),
                MINUTE
            )

Thanks @MarcelWoodman ! It works now but the work hours is incorrect.

workhours.PNG

Below is the column i created. Is there any error? Please help. Thanks.

Work Hour = 
VAR workcal =
FILTER (
ADDCOLUMNS (
CROSSJOIN (
CALENDAR ( DATEVALUE([Start Date]), DATEVALUE([End Date]) ),
SELECTCOLUMNS ( GENERATESERIES ( 8, 17 ), "Hour", [Value] )
),
"Day of week", WEEKDAY ( [Date], 2 )
),
[Day of week] < 6
)
VAR hourcount =
COUNTROWS(
FILTER (workcal,
(
([Date] = DATEVALUE( [Start Date] )
&&
[Hour] > HOUR( [Start Date] ))
&&
([Date] = DATEVALUE( [End Date] )
&&
[Hour] <= HOUR( [End Date] ))
)
||
([Date] > [Start Date] && [Date] < [End Date] )
)
)
VAR remained =
if(
DATEVALUE([Start Date]) = DATEVALUE([End Date]) && HOUR([Start Date]) = HOUR([End Date]),
DATEDIFF([Start Date],[End Date],MINUTE),
DATEDIFF (
TIMEVALUE ( [Start Date] ),
TIME ( HOUR ( [Start Date] ) + 1, 0, 0 ),
MINUTE
)
+
DATEDIFF (
TIME ( IF ( HOUR ([End Date] ) = 0, 23, HOUR ([End Date] ) - 1), 0, 0 ),
TIMEVALUE ( [End Date] ),
MINUTE
)
)
VAR worktime = (hourcount*60 + remained)/60
RETURN
worktime

@PBI_newuser, so my previous code was terrible 😁

 

Not sure if this completely works, but it does seem at least *more* accurate:

BusinessHours = 
VAR Start_Date = [DateTime]
VAR End_Date = [DateTime]
VAR Start_Hour = 8
VAR End_Hour = 17
VAR workcal =
    FILTER (
        ADDCOLUMNS (
            CROSSJOIN (
                CALENDAR ( DATEVALUE(Start_Date), DATEVALUE(End_Date) ),
                SELECTCOLUMNS ( GENERATESERIES ( Start_Hour, End_Hour ), "Hour", [Value] )
            ),
            "Day of week", WEEKDAY ( [Date], 2 )
        ),
        [Day of week] < 6
    )
VAR starthourcount = 
    COUNTROWS(
        FILTER (workcal,
            (
            [Date] = DATEVALUE(Start_Date)
                && 
                [Hour] > HOUR(Start_Date)
            )
        )
    )
VAR endhourcount = 
    COUNTROWS(
        FILTER (workcal,
            (
            [Date] = DATEVALUE(End_Date) 
                && 
                [Hour] < HOUR(End_Date)
            )
        )
    )
VAR daycount = 
    COUNTROWS(
        FILTER (workcal,
            ([Date] > DATEVALUE(Start_Date) && [Date] < DATEVALUE(End_Date) )
        )
    )
VAR remained = 
    if(
        DATEVALUE(Start_Date) = DATEVALUE(End_Date) && HOUR(Start_Date) = HOUR(End_Date), 
            DATEDIFF(Start_Date,End_Date,MINUTE),
            IF(
				HOUR (Start_Date) < Start_Hour || HOUR (Start_Date) >= End_Hour, 0,
				DATEDIFF (
					TIMEVALUE ( Start_Date ),
					TIME ( HOUR ( Start_Date ) + 1, 0, 0 ),
					MINUTE
				)
			)
            +
			IF(
				HOUR (End_Date) < Start_Hour || HOUR (End_Date) >= End_Hour, 0,
				DATEDIFF (
					TIME ( HOUR ( End_Date ), 0, 0 ),
					TIMEVALUE ( End_Date ),
					MINUTE
				)
			)
    )
VAR worktime = ((daycount + starthourcount + endhourcount)*60 + remained)/60
RETURN
    worktime

Hi @MarcelWoodman 

do you have a formula to remove the cases in which start date>end date??

I would like to have these dates out of the calculation

 

Thanks a lot in advance!
Alessandra

Anonymous
Not applicable

Thanks @MarcelWoodman your solution gave me a great start to my own particular problem of a business hours calculation for two columns that are a ticket opening date/time and a ticket response date/time. 

 

A noticable problem to overcome was the effect of the calculation when open and response happen on the same day compared to when the response is the next day or later. To solve this I had to create 3 new columns altogether, one that is suited to a same day response, one that is suited to a next day or later response and one for the required result of business response duration (measured in minutes in my case) :

 

 

 

sameday_response_minutes = 
/* This calculates the number of busines minutes between ticket open and being accepted when both occur on the same day */

VAR Start_Date = 'Data Table'[opened]
VAR End_Date = 'Data Table'[responded]
VAR Start_Hour = 8
VAR End_Hour = 17

/* calculate the number of full business hours on the day */

VAR first_day_hours_cal = 
	FILTER (
		ADDCOLUMNS (
			GENERATESERIES ( Start_Hour, End_Hour ), "Hour", [Value]), ((hour(End_Date)- [Hour])>0 && hour(End_Date)>hour(Start_Date) && hour(Start_Date)<[Hour]  ))
			

VAR first_day_hours = COUNTROWS(first_day_hours_cal)

/* calculate the number of full business minutes of the first hour */

VAR first_day_minutes = 60 - MINUTE(Start_Date)

/* calculate the number of full business seconds of the first hour */
VAR first_day_seconds = (60 - SECOND(Start_Date))/60

/* calculate the number of full business minutes of the last hour */
VAR last_day_minutes = if(hour(End_Date)>Start_Hour && HOUR(End_Date)<End_Hour,MINUTE(End_Date),0)
/* calculate the number of full business seconds of the last hour */
VAR last_day_seconds = if(hour(End_Date)>Start_Hour && HOUR(End_Date)<End_Hour,SECOND(End_Date),0)

/* add them all up to get the response time in minutes under different conditions 1. when the response is at the same moment as the ticket is opened, 2. when the ticket is opened and responded out of business hours, 3. at the weekend, 4. when the start and response are both during business hours, 5. when ticket is opened or responded outside of business hours */

VAR response_time=if(Start_Date=End_Date,0,if(HOUR(Start_Date)>End_Hour && HOUR(End_Date)>End_Hour,0,(if(WEEKDAY(Start_date,2)>5,0,if((hour(Start_Date)>Start_Hour && hour(End_Date)<End_Hour),((time(hour(End_Date),MINUTE(End_Date),SECOND(End_Date))-TIME(HOUR(Start_Date),MINUTE(Start_Date),second(Start_Date)))*(24*60)),( (first_day_hours*60) + first_day_minutes - first_day_seconds + last_day_minutes + last_day_seconds))))))

RETURN
    response_time

 

 

 

 

 

slow_response_minutes = 
/* this calculates the business minutes between ticket opening and ticket being accepted when the response happens on the next day or later */

VAR Start_Date = 'Data Table'[opened]
VAR End_Date = 'Data Table'[responded]
VAR Start_Hour = 8
VAR End_Hour = 17

/* calculate the number of full business days between open and response */
VAR full_days_table =
    FILTER (
        ADDCOLUMNS (

                CALENDAR ( Start_Date, End_Date), "Day of week", WEEKDAY ( [Date], 2 ) 
        ),
        [Day of week] < 6
    )   
VAR full_daycount = 
    COUNTROWS(
        FILTER (full_days_table,
            ([Date] > DATEVALUE(Start_Date) && [Date] < DATEVALUE(End_Date) )
        )
    )
	
/* calculate the number of full business hours on the start day */

VAR first_day_hours_table = 
	FILTER (
		ADDCOLUMNS (
			GENERATESERIES ( Start_Hour, End_Hour ), "Hour", [Value]), (hour(Start_Date) < [Hour] && hour(Start_Date)>=Start_Hour) )
			

VAR first_day_hours = COUNTROWS(first_day_hours_table)

/* calculate the number of full business minutes on the start day */

VAR first_day_minutes = 60 - MINUTE(Start_Date)

/* calculate the number of full business seconds on the start day */
VAR first_day_seconds = (60 - SECOND(Start_Date))/60

/* calculate the number of full business hours on the end day */
VAR last_day_hours_cal = 
	FILTER (
		ADDCOLUMNS (
			GENERATESERIES ( Start_Hour, End_Hour ), "Hour", [Value]), (hour(End_Date) > [Hour] && hour(End_Date)<=End_Hour) )
			

VAR last_day_hours = COUNTROWS(last_day_hours_cal)

/* calculate the number of full business minutes on the end day */
VAR last_day_minutes = if(hour(End_Date)>Start_Hour && HOUR(End_Date)<End_Hour,MINUTE(End_Date),0)
/* calculate the number of full business seconds on the end day */
VAR last_day_seconds = if(hour(End_Date)>Start_Hour && HOUR(End_Date)<End_Hour,SECOND(End_Date),0)



/* add them all up to get the response time in minutes */

VAR response_time =  (full_daycount*(End_Hour-Start_Hour)*60) + (first_day_hours*60) + first_day_minutes - first_day_seconds + (last_day_hours*60) + last_day_minutes + last_day_seconds


RETURN
    response_time

 

 

 

 

 

response_time_minutes = if(DATEDIFF('Data Table'[opened],'Data Table'[responded],DAY)=0,'Data Table'[sameday_response_minutes],'Data Table'[slow_response_minutes])

 

 

 

So far I haven't found any mathematical errors but when the report is implemented I'll monitor it for any scenarios that haven't been accounted for.

 

 

@MarcelWoodman Thanks a lot for your help! But it still doesn't work for me. 

business hours.png

@PBI_newuser, so honestly, I'm not sure. I ran into a lot of similar issues when coming up with that code, because there are a number of exceptions that need to be accounted for.

 

One thing that I did to help was break the functions up piecemeal so that I could see where the errors were coming from, and then worked the code back into a single function. I think I'm going to have to go back and check my data to see if it's also pulling the wrong hour count.

Netjacker65
Frequent Visitor

try this simple solution:

 

//New Column

Working Hour = DATEDIFF ( [ACTIVITY_DATE], [LASTMODIFIEDDATE], HOUR )

Anonymous
Not applicable

Hi,

 

Great to see this model, I can definitively use it. But had one more question:

 

Any idea how to deal with varying opening hours per day of the week? (e.g. Monday 09:00-18:00, Tuesday 10:00-21:00)

 

Any help is much appreciated!

v-shex-msft
Community Support
Community Support

 

HI @rocky09,

 

You can try to use below calculated column formula to calculate valid working hour:

 

Work Hour = 
VAR filtered =
    FILTER (
        ADDCOLUMNS (
            CROSSJOIN (
                CALENDAR ( [ACTIVITY_DATE], [LASTMODIFIEDDATE] ),
                SELECTCOLUMNS ( GENERATESERIES ( 9, 18 ), "Hour", [Value] )
            ),
            "Day of week", WEEKDAY ( [Date], 2 )
        ),
        [Day of week] < 6
            && [TicketID] = EARLIER ( Table1[TicketID] )
    )
VAR hourcount =
    COUNTROWS (
        FILTER (
            filtered,
            (
                [Date] >= DATEVALUE ( [ACTIVITY_DATE] )
                    && [Hour]
                        > HOUR ( [ACTIVITY_DATE] ) + 1
            )
                && (
                    [Date] <= DATEVALUE ( [LASTMODIFIEDDATE] )
                        && [Hour]
                            > HOUR ( [LASTMODIFIEDDATE] ) - 1
                )
        )
    )
VAR remained =
    DATEDIFF (
        TIMEVALUE ( [ACTIVITY_DATE] ),
        TIME ( HOUR ( [ACTIVITY_DATE] ) + 1, 0, 0 ),
        MINUTE
    )
        + DATEDIFF (
            TIME ( HOUR ( [LASTMODIFIEDDATE] ) - 1, 0, 0 ),
            TIMEVALUE ( [LASTMODIFIEDDATE] ),
            MINUTE
        )
RETURN
    IF ( hourcount <> BLANK (), (hourcount*60 + remained)/60, 0 )

 2.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

Hi

 

I am also getting the "The start date in Calendar function can not be later than the end date" error

but when I change my workiung hours this way, as in the code section I do not get the error BUT obviously have 0's as that is not working time

SELECTCOLUMNS ( GENERATESERIES (17,6 ), "Hour", [Value] )
Anonymous
Not applicable

@v-shex-msft

 

I was looking at your code, and I think it is probably going to be pretty useful for what I'm doing.

 

I was wondering if it would be possible to consider more than one "range" of working hours (I want to take out the lunch time) without having to create multiple columns and then adding them, just to make the code cleaner.

 

In my case, the time range would be like:

- 8:00 - 12:00

- 13:00 - 17:15

 

Thank you in advance!

Anonymous
Not applicable

I am getting the following error. Any idea how to fix it? 

 

"An argument of function 'TIME' has the wrong data type or the result is too large or too small."

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.