Dax Function: TIMEVALUE

Category: Date and Time Functions

The TIMEVALUE function in Power BI converts a text string representing a time into a datetime value.

Purpose

To parse and transform textual time representations into proper datetime formats for calculations and analysis.

Type of Calculations

Converts text-based time values (e.g., “14:30:00”) into a standard datetime format recognizable by DAX.

Practical Use Cases

  • Parsing time data imported as text.
  • Standardizing inconsistent time formats for calculations.
  • Enabling time-based analysis on datasets with mixed time formats.

TIMEVALUE(<time_text>)</time_text>
ParameterTypeDescription
time_textText/ScalarA text string that represents a time. Must be in a recognizable time format (e.g., “hh:mm:ss”).

How Does TIMEVALUE Dax Function Works

The TIMEVALUE function interprets the time_text parameter and converts it into a time value by parsing the string based on recognizable time formats. If the input string includes invalid or unrecognized time formats, the function returns an error.

Key Behaviors:

  • If the input is "14:30:00", the function interprets it as 2:30:00 PM.
  • If the input contains leading or trailing spaces, it trims them automatically.
  • The date portion of the output is always set to the default date (12/30/1899).

What Does It Return?

  • Type: Datetime.
  • Meaning: Returns a datetime value representing the time portion extracted from the input string. The date defaults to 12/30/1899 (Excel’s epoch date).

When Should We Use It?

  • Converting imported time data stored as text into a format suitable for time calculations.
  • Parsing user-inputted or external text files with time values.
  • Standardizing time data for reporting dashboards and charts.

Examples

Basic Usage

Convert a simple time string to a datetime value:


ParsedTime = TIMEVALUE("14:45:00")

Output: 2:45:00 PM (date defaults to 12/30/1899).

Column Usage:

Parse time strings from a column:


StandardizedTime = TIMEVALUE(Table[TimeString])

Output: Returns a datetime value for each row where Table[TimeString] contains a valid time.

Advanced Usage

Use TIMEVALUE with other functions to handle edge cases:


ParsedTimeWithFallback =
IF(
ISERROR(TIMEVALUE(Table[TimeString])),
BLANK(),
TIMEVALUE(Table[TimeString])
)

Output: Returns blank for invalid time strings; otherwise, it parses the time.

Tips and Tricks

  • Validate Inputs: Use functions like ISERROR to handle invalid or inconsistent text inputs gracefully.
  • Handling Dates: If the input contains both date and time, only the time portion is extracted.
  • Time Zones: The function does not handle time zones; additional processing may be needed for time zone-sensitive datasets.
  • Formatting: Use Power BI’s formatting options to display only the time portion in visualizations.

Potential Pitfalls

  • Invalid Formats: The function only works with valid and recognizable time strings. For example, "25:00:00" will cause an error.
  • Trailing Characters: Non-time characters in the string (e.g., "14:00:00abc") will cause parsing errors.
  • Performance: Parsing text in large datasets can be computationally expensive. Preprocess text data where possible.

Performance Impact of TIMEVALUE DAX Function:

  • Use preprocessing to clean and validate text data for better performance in large datasets.
  • Avoid using TIMEVALUE in calculated columns on very large tables; prefer measures where feasible.

Related Functions You Might Need

  • DATEVALUE: Converts a date string to a date value.
  • TIME: Constructs a time value from hour, minute, and second inputs.
  • HOUR: Extracts the hour from a datetime value.
  • MINUTE: Extracts the minute from a datetime value.
  • SECOND: Extracts the second from a datetime value.

Want to Learn More?
For more information, check out the official Microsoft documentation for TIMEVALUE You can also experiment with this function in your Power BI reports to explore its capabilities.

Unlock the full capabilities of Power BI and elevate your data insights with our specialized consulting services. Whether you need guidance on advanced DAX functions like those highlighted here, support in designing interactive dashboards, or expertise in optimizing data models for enhanced performance, our experienced Power BI consultants are equipped to deliver customized solutions for your business. Explore our Power BI Consulting Services page to discover how we can help your organization make smarter, data-driven decisions.

1. What does the TIMEVALUE function in Power BI do?

It converts a text string representing a time into a datetime value.

2. Can TIMEVALUE handle invalid time strings?

No, invalid or unrecognized time strings will cause an error. Use validation functions to handle such cases.

3. Does TIMEVALUE consider time zones?

No, it does not. The function only parses the time string as-is.

4. What happens if a date is included in the input?

The function extracts and returns only the time portion of the input string.

5. How can I handle inconsistent input formats with TIMEVALUE?

Use string manipulation functions like TRIM, SUBSTITUTE, or LEFT to preprocess the text before applying TIMEVALUE.

Previous article
Dax Function: TIME
Next article
Dax Function: TODAY