Ravindra BagaleCourses & study guides मराठी Track your progress

7. Data Cleaning A–Z in Power Query

7.14 Dates and Times: Parts, Split DateTime, UTC to IST, Age and Duration

By Last updated:

Start with a problem

Order times arrive in UTC. Order BLK-402 shows 06-Apr 18:40, but in Pune it was 00:10 on 07-Apr. Daily IST totals are wrong.

Explain simply

Mark the value as UTC, switch to IST (+05:30), then remove the zone. Calculate durations by subtracting two datetimes.

Show the data

Before:

Order ID Order Time UTC Delivered Time UTC
BLK-401 06-04-2026 02:15 06-04-2026 02:24
BLK-402 06-04-2026 18:40 06-04-2026 18:52
BLK-403 07-04-2026 13:05 07-04-2026 13:21

About this data: This table uses columns: Order ID, Order Time UTC, Delivered Time UTC. This table uses columns: IST Date, Orders. This table uses columns: From Order DateTime 2025-10-20 21:47, Menu path, Result. This table uses columns: Order ID, Order Time UTC. Values shown are the lesson examples already on this page (not a new dataset).

Fictional sample data. Practice file: pq_order_times_utc.csv (source: src/practice/).

Demonstrate

  1. Set both columns to Date/Time (the file uses yyyy-mm-dd, so any locale reads it).

  2. Add Custom Column Order Time IST = DateTimeZone.RemoveZone(DateTimeZone.SwitchZone(DateTime.AddZone([Order Time UTC], 0), 5, 30)); type Date/Time.

    Your result should look like this:

    Order Time IST
    06-Apr 07:45
    07-Apr 00:10
    07-Apr 18:35

  3. Add Column › Date › Date Only on Order Time IST.

    Your result should look like this:

    IST Date
    06-Apr-2026
    07-Apr-2026
    07-Apr-2026

  4. Add Custom Column Delivery Mins = Duration.TotalMinutes([Delivered Time UTC] - [Order Time UTC]).

    Your result should look like this:

    Delivery Mins
    9
    12
    16

Show the result

UTC to IST conversion changing the date, sample layout with fictional data

Your result should look like this (fictional sample):

IST Date Orders
06-Apr-2026 (Monday) 1
07-Apr-2026 (Tuesday) 2

Explain what happened

UTC 18:40 + 5:30 = 00:10 next day, so BLK-402 moves to 07-Apr. Durations do not change with the zone because both times are in UTC.

Explore further

  • Order Hour (IST) = Time.Hour: 7, 0, 18.

Detailed reference for this lesson:

Extract parts with Add Column › Date / Time (or Transform tab to replace):

From Order DateTime 2025-10-20 21:47 Menu path Result
Date only Add Column › Date › Date Only 20-10-2025
Time only Add Column › Time › Time Only 21:47:00
Year Date › Year › Year 2025
Month name Date › Month › Name of Month October
Quarter Date › Quarter › Quarter of Year 4
Week of year Date › Week › Week of Year 43
Day name Date › Day › Name of Day Monday
Hour Time › Hour › Hour 21
Start of month Date › Month › Start of Month 01-10-2025

Split a DateTime. Select Order DateTime › Add Column › Date › Date Only, then Add Column › Time › Hour › Hour. Relate Order Date to the Date table and use Order Hour for "orders by hour of day" charts (Module 14).

UTC to IST

Before (UTC)

Order ID Order Time UTC
AMZ-50001 2025-10-20 18:40
AMZ-50002 2025-10-20 19:10

After (IST = UTC + 5:30)

Order ID Order DateTime IST Order Date
AMZ-50001 2025-10-21 00:10 21-10-2025
AMZ-50002 2025-10-21 00:40 21-10-2025

Notice that both orders move to the next day in IST. If you ignore time zones, late-night Diwali orders are counted on the wrong date.

Steps in Power BI

  1. Make sure the UTC column is Date/Time.
  2. Add Column › Custom Column › name Order DateTime IST › formula below.
  3. Set its type to Date/Time, then Add Column › Date › Date Only to get Order Date in IST.
IST = Table.AddColumn(Source, "Order DateTime IST", each
    DateTimeZone.RemoveZone(
        DateTimeZone.SwitchZone(DateTime.AddZone([Order Time UTC], 0), 5, 30)),
    type datetime)
// simple alternative (India has no daylight saving):
// each [Order Time UTC] + #duration(0, 5, 30, 0)

Age and duration

  • Customer tenure/age: select Signup Date › Add Column › Date › Age. This gives a duration from that date until now. Then use Transform › Duration › Total Years (or Days).
  • Delivery duration: Add Column › Custom Column [Delivered DateTime] - [Order DateTime] returns a duration such as 0.00:09:30. Then Add Column › Duration › Total Minutes gives 9.5.
Dur  = Table.AddColumn(Source, "Delivery Duration", each [Delivered DateTime] - [Order DateTime], type duration),
Mins = Table.AddColumn(Dur, "Delivery Mins (calc)", each Duration.TotalMinutes([Delivery Duration]), type number)

Ravindra Bagale's Tip

Friends, don't make a mistake here: relating an Order DateTime column (with time) to the Date table is a very common mistake, because nothing matches except midnight. Always create a date-only column for relationships. Also remember that Age calculated from "now" changes on every refresh, so compare with a fixed date column when you need stable numbers. Practise, and it will feel very easy.

Practice task

For Amazon Now Nagpur orders stored in UTC, create Order DateTime IST, Order Date, Order Hour and Day Name. Check that an order at 2025-08-26 19:00 UTC falls on 27-08-2025 in IST.

Fix common mistakes

Wrong result Reason Correction
BLK-402 counted on 06-Apr Used UTC date Convert to IST before taking the date
Added 5.5 hours as number Used + 5.5 Use SwitchZone(…, 5, 30) or #duration(0,5,30,0)
Delivery Mins = 0.00625 Subtraction returned days Duration.TotalMinutes

Practise independently

Practice task

Using UTC dates, how many orders fall on 06-Apr? Using IST?

Hints
  • Compare columns.
Solution

UTC: 2 (BLK-401, 402). IST: 1 (BLK-401).

Think like a professional

State the time zone in every date column name or description – IST vs UTC confusion causes real reporting incidents.

Ravi Sir's Cards for this lesson

Dates and times: parts, split, UTC to IST. App data is often stored in UTC. Without converting, evening orders land on the wrong day.Revision card

मराठी cards