How to add and subtract hours and minutes to a datetime in Power BI?

by

Here’s a question from Kevin:

I have a date time column in one of my Power BI data model table representing a timestamp. I would like to create a few additional columns to the table each of those will increment the first column by 08:30 hours, which is our default shift duration. Any ideas on how to do this as i do not find a suitable DAX formula?

Add time to a datetime column in Power BI

Creating a column of timestamps

If you don’t have a date time filed defined in your table, and want to follow along with this tutorial you can start by creating a very simple date column using the CALENDAR function in DAX:

Dates = CALENDAR (DATE (2024,1,20), DATE(2024,2,5))

Important Note: If you are loading your own data from an external file of database table, make sure that the date type of your column is of type date or date / time.

Adding time to a timestamp in Power BI

To offset a time value in Power BI, use the DAX function TIME. The syntax is simple:

New_Time_Column  = Existing_Time_Column  + TIME(offset in hours, offset in minutes, offset in seconds)
  • In our case you would like to add 08:30 hours. Start by navigating to the Data tab.
  • Then hit the New Column button.
  • Next type the following DAX:
First Shift Start = Dates[Date]+TIME(8,30,0)

This will create a new calculated column as shown below:

Troubleshooting: avoid mismatched data types or invalid time value error messages by ensuring that the data type column you will be adding or subtracting values from is a date / time.

Subtract hours, minutes and seconds in Power BI

In the same fashion we can also subtract time:

  • In your Power BI report, navigate to the Data View.
  • Then hit New Column.
  • Type the following DAX and hit Enter.
Prep Start = Dates[Date] - TIME(2,30,0)
  • This expression creates a new calculated column containing a timestamp that deducts 2:30 from our Date Time column.
  • Here’s our table:
  • Save your report.

Extract Time values from DateTime fields in PowerBI

Another common question we get is how to display only the time portion from a datetime field in my HR attendance report?

To extract time from a datetime field in Power BI, follow either of these steps:

  1. First go ahead and create a new measure:
   Formatted Time = TIME(HOUR('Table'[DateTime]), MINUTE('Table'[DateTime]), SECOND('Table'[DateTime]))

This DAX formula uses the TIME function with HOUR, MINUTE, and SECOND parameters to extract the time components.

  1. A second options is:
   Formatted Time = FORMAT('Table'[DateTime], "hh:mm:ss AM/PM")
  1. To calculate time differences, use DATEDIFF:
   Hours Worked = DATEDIFF('Table'[ClockIn], 'Table'[ClockOut], HOUR)

Example

Once you have your time values, you can use them in a report. For Example: Create an “Employee Time Report” visual showing clock-in times and hours worked.

  1. Drag the “Employee Name” field to Rows.
  2. Add “Formatted Time” to Values for clock-in time.
  3. Add “Hours Worked” to Values for duration.

Troubleshooting

  • Ensure your source data is in a valid datetime format.
  • Check for blank or null values that might affect calculations.
  • Verify your system locale settings if AM/PM formatting is incorrect.

Add and Subtract Time in Power Query

To add or subtract time in Power Query, we’ll work with a data model containing datetime columns. Follow the steps outline below:

  1. Load your data (csv, sql, json, excel, nosql) into Power BI and open Power Query Editor.
  2. To add time, create a custom column with this formula:
   = [Timestamp] + #duration(0, 2, 30, 0)

This adds 2 hours and 30 minutes to the Timestamp column.

  1. To subtract time, use:
   = [Timestamp] - #duration(0, 1, 15, 0)

This subtracts 1 hour and 15 minutes from the Timestamp column.

  1. Rename your new columns appropriately (e.g., “Adjusted Time”).

The #duration function uses the format (days, hours, minutes, seconds). You can adjust these values as needed.

Here’s another example showcasing adjusting clock-in times for shift workers:

  1. Create a column for early arrivals:
   = if [ClockIn] < [ShiftStart] then [ShiftStart] else [ClockIn]
  1. Create a column for late departures:
   = if [ClockOut] > [ShiftEnd] then [ShiftEnd] else [ClockOut]

These steps allow HR to accurately calculate work hours within scheduled shifts.