STARTOFQUARTER & ENDOFQUARTER - Date Only

Hi StuartSmith ,

 

The FORMAT function returns the text format, and the date cannot be compared with the text.

I think you want to return 1 if the year, month and day are equal, otherwise 0, ignore hours, minutes, seconds.

As follows, the first row should also return 1.

You can create two calcualted columns to extract only dates with year, month and day.

Date new = 
var _date=[Date]
return DATE(YEAR(_date),MONTH(_date),DAY(_date))

Date1 new = 
var _date=[Date1]
return DATE(YEAR(_date),MONTH(_date),DAY(_date))

If you want to display only short formats, you can change as follows.

With a new comparison of the two date columns, this time the results are returned correctly. Even if the formats are different, the results are returned correctly. Because the underlying data only contains the year, month and day, unlike the original one, it will compare hours, minutes, and seconds.

 

Hopefully, the above examples can help you understand and solve the problem.

To summarize, you can use the DATE function to create your FirstQuarterDate and LaseQuarterDate. Then if your _End Date] contains different hours, minutes, and seconds, it is recommended to use the DATE function to extract the year, month and day as well.

Of course, if your data can be opened in Power Query, there is an easier way.

Go to Power Query, select the date column and choose Date Only.

 

                                                                                                                                                         

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.