Time Range

[Yuvalokon]

Hi, I wrote the following formula and created a custom column: =if[AccountCreationTime]>=[Terms_Shay.Leads Start Time] and [AccountCreationTime]<=[Terms_Shay.Leads End Time] then 1 else 0 The result I get is that 0:59:45 (first line) is not in the range of hours even though the range is between 17:00:00 and 01:00:00 in the morning.

Thanks for the helpers.

[AlB]

@Yuvalokon 

The time data type only spans 24h so everything is considered in the same day by PQ. You would need additional data to specify if that is not the case. If the time is on the current date as you say, your initial code should work.

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[AlB]

@Yuvalokon 

It could be updated to work but a much safer and more reasonable option would be to include the dates, so that you have datetimes instead of only times. Otherwise there's ambiguity in the data. For your first row, why does the result have to be 1? You are assuming the 00:59:00 is on the following day and in that case the result would be one but why wouldn't it be in the current day, resulting in a 0? You cannot tell form the data as it currently is.

Including the date would eliminate the ambiguity and your initial code would work.

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[Yuvalokon]

Hi @AlB ,

In case when 1:00:00 is for the previous date so how the formula should be?

Example:

Start Time: 17:00:00

End Time : 01:00:00

Actual Time : 00:59:59

[AlB]

Hi @Yuvalokon 

The problem is that in that case PQ interprets 01:00:00 as being in the same day but you want it to be in the following day. Try:

if [AccountCreationTime] >= [Terms_Shay.Leads Start Time] and [AccountCreationTime] <= [Terms_Shay.Leads End Time] or
([AccountCreationTime] >= [Terms_Shay.Leads Start Time] and [AccountCreationTime] < [Terms_Shay.Leads Start Time] + #duration(1,0,0,0)) then 1 else 0

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[Yuvalokon]

@AlB 

It still doesn't work for me :

if [AccountCreationTime] >= [Terms_Shay.Leads Start Time] and [AccountCreationTime] <= [Terms_Shay.Leads End Time] or
([AccountCreationTime] >= [Terms_Shay.Leads Start Time] and [AccountCreationTime] <= [Terms_Shay.Leads Start Time] + #duration(1,0,0,0)) then 1 else 0