topic Re: a column that uses h:nn format and also trying to have a calculated column that refers to that c in Desktop

a column that uses h:nn format and also trying to have a calculated column that refers to that colum

quintans1 — Thu, 16 Oct 2025 21:05:56 GMT

I have a column(lets call it TimeDiff) where I take the difference of end time and start time and then use format h:nn

so for example one of the results in TimeDiff is 19:45 (19 hours and 45 minutes)

but now i want to create a calculated column that looks at all the values in TimeDiff and says if the TimeDiff is greater than 8 hours then subtract the TimeDiff value minus 8 hours (i.e., 19:45 would show as 11:45)

Trying to write this gets me Expressions that yield variant data-type cannot be used to define calculated columns

I can not provide a pbix file and think I may have to pull apart the hours and minutes to then reconstruct what I need so I would appreciate any guidance or suggestions.

Thank you

Re: a column that uses h:nn format and also trying to have a calculated column that refers to that c

lbendlin — Fri, 17 Oct 2025 01:48:02 GMT

store your timevalues as the fractions that they are (days are integers, times are fractions). Then do your math, and then only at the very last moment use FORMAT to display in the desired format. Keep in mind that your math can result in more than 24 hours in which case you need to decide how you want to render that result.

Re: a column that uses h:nn format and also trying to have a calculated column that refers to that c

v-sshirivolu — Fri, 17 Oct 2025 09:50:51 GMT

Hi @quintans1 ,

I would also take a moment to thank @lbendlin , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions

Re: a column that uses h:nn format and also trying to have a calculated column that refers to that c

Shubham_rai955 — Fri, 17 Oct 2025 10:23:16 GMT

This calculated column extracts the numeric hour and minute parts from the "TimeDiff" column, which is in "h:mm" text format.
It converts these to total minutes to perform numeric time comparison and arithmetic.
If the total minutes exceed 8 hours (480 minutes), it subtracts 480 minutes (8 hours) from the total.
The result is then converted back to "h:mm" text format for readability.
This approach avoids Power BI's variant data-type errors by keeping operations numeric until the final text formatting step.
// It also ensures correct handling of time durations greater than 8 hours while preserving the original format style where applicable.

Re: a column that uses h:nn format and also trying to have a calculated column that refers to that c

quintans1 — Sat, 18 Oct 2025 13:13:03 GMT

Thank you for your suggestions, but I guess I will do it another way.