Date/time Formatting differently

[hsackman]

Hello, 

My data is for some reason formatting date/time diffferently in my import.  I'd like to compare the hours/minutes/seconds to determine if it is exceding a threshold (different thresholds throghout dataset). Essentially, comparing TALK TIME to Minimum Duration, shown in example below. For some reason, some are loading as 12/31/1899 and some are loading as 12/30/1899. Not sure what's causing this.  Any suggestions?

[Anonymous · Accepted solution]

Hi @hsackman ,

The date "12/31/1899" or "12/30/1899" is typically a placeholder used by some systems to represent a time-only value. When you convert a column of values that only have time value but no date value to date/time, 1899/12/30 or 1899/12/31 will appear. The discrepancy you're seeing could be due to different time zones or daylight saving time adjustments.
I'll give you some advice on what's next:
1. Ensure that the time zone settings are consistent across all systems involved in the data import process. This includes your local machine, the server, and any other systems that might be interacting with your data.
2. If all the times in the column are on the same day, that is, there is no cross-date time comparison, you can modify the data type of the data to time instead of date/time:

3. If your data spans across periods of daylight saving time, this could be causing the one-day discrepancy. You might need to adjust your data or calculations to account for this.

If the above doesn't help you solve the problem, it would be helpful if you could provide sample data and some details.

Best Regards,

Dino Tao

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

[hsackman]

This doesn't help because any date that is listed as 12/30/1899 is still less than the threshold, even though the hours/minutes are greater. e.g. a 12/30 date will still be <1.  I need all dates in the Talk Time column to be the same.

[PijushRoy]

Hi @hsackman 

Please change the format to Decimal number in Power Query editor