Forum Discussion
Expression.Error: We cannot apply operator < to types Time and Number
Hi
I have a data set with start and end time. I am trying to work out the duration within set time frames, i.e. 12am to 9am, 9am to 5pm, 5pm to 11pm and 11pm to 12pm.
I have broken each range down into columns to make it simpler. This is the formula for the12 am to 9 am column but anything I try I can't seem to get a result. There are no syntax errors, just no result.
if (Time.Hour([Start Time]) >=Time.FromText("00:00:00am") and [Start Time]<Time.FromText("09:00:00am"))
and Time.Hour([End Time])<Time.FromText("09:00:00am")
then if [End Time] > [Start Time] then [End Time] - [Start Time] else 1-[Start Time] + [End Time]
else null
Thanks
You have qualified Start Time with Time.Hour which is gives a number output which then you are compring with time and this gives the error. Both side should be having same unit, hence either they should have time on both side or number on both side.
Approach 1 - When time on both side
= if [Start Time] >=Time.FromText("00:00:00am") and [Start Time]<Time.FromText("09:00:00am") and [End Time]<Time.FromText("09:00:00am") then if [End Time] > [Start Time] then [End Time] - [Start Time] else 1-[Start Time] + [End Time] else nullApproach 2 - When number on both side
= if Time.Hour([Start Time]) >=0 and Time.Hour([Start Time])<9 and Time.Hour([End Time])<9 then if [End Time] > [Start Time] then [End Time] - [Start Time] else 1-[Start Time] + [End Time] else null
2 Replies
You have qualified Start Time with Time.Hour which is gives a number output which then you are compring with time and this gives the error. Both side should be having same unit, hence either they should have time on both side or number on both side.
Approach 1 - When time on both side
= if [Start Time] >=Time.FromText("00:00:00am") and [Start Time]<Time.FromText("09:00:00am") and [End Time]<Time.FromText("09:00:00am") then if [End Time] > [Start Time] then [End Time] - [Start Time] else 1-[Start Time] + [End Time] else nullApproach 2 - When number on both side
= if Time.Hour([Start Time]) >=0 and Time.Hour([Start Time])<9 and Time.Hour([End Time])<9 then if [End Time] > [Start Time] then [End Time] - [Start Time] else 1-[Start Time] + [End Time] else nullThanks so much Vijay_A_Verma, and thanks for giving both options - it is really helping learn as I'm only just starting out with Power BI.
