Forum Discussion

Dicken's avatar
Dicken
Icon for Post Prodigy rankPost Prodigy
15 days ago
Solved

create total hours from date time



 

General Comment
  • Praful_Potphode's avatar
    Praful_Potphode
    7 days ago

    Hi Dicken​ 

    try sample mcode

    let
    Source = #table(
        type table [Task = text, Start = datetime, End = datetime],
        {
            {"Task A", #datetime(2026, 10, 1, 9, 0, 0),  #datetime(2026, 10, 1, 17, 30, 0)},
            {"Task B", #datetime(2026, 10, 1, 22, 0, 0), #datetime(2026, 10, 2, 6, 15, 0)},
            {"Task C", #datetime(2026, 10, 2, 8, 0, 0),  #datetime(2026, 10, 5, 12, 0, 0)},
            {"Task D", #datetime(2026, 10, 3, 14, 45, 0), #datetime(2026, 10, 3, 15, 5, 0)},
            {"Task E", #datetime(2026, 10, 4, 10, 0, 0), null}
        }
    ),
    AddTotalHours = Table.AddColumn(
        Source,
        "Total Hours",
        each if [Start] = null or [End] = null
            then null
            else Number.Round(Duration.TotalHours([End] - [Start]), 2),
        type number
    )
in
    AddTotalHours

    Please give kudos or mark it as resolved once confirmed.

    Regards,

    Praful

9 Replies

  • v-saisrao-msft's avatar
    v-saisrao-msft
    Icon for Community Support rankCommunity Support
    5 days ago

    HI Dicken​,

    Checking in to see if your issue has been resolved. let us know if you still need any assistance.

    Thank you.

  • Praful_Potphode's avatar
    Praful_Potphode
    Icon for Super User rankSuper User
    7 days ago

    Hi Dicken​ 

    try sample mcode

    let
    Source = #table(
        type table [Task = text, Start = datetime, End = datetime],
        {
            {"Task A", #datetime(2026, 10, 1, 9, 0, 0),  #datetime(2026, 10, 1, 17, 30, 0)},
            {"Task B", #datetime(2026, 10, 1, 22, 0, 0), #datetime(2026, 10, 2, 6, 15, 0)},
            {"Task C", #datetime(2026, 10, 2, 8, 0, 0),  #datetime(2026, 10, 5, 12, 0, 0)},
            {"Task D", #datetime(2026, 10, 3, 14, 45, 0), #datetime(2026, 10, 3, 15, 5, 0)},
            {"Task E", #datetime(2026, 10, 4, 10, 0, 0), null}
        }
    ),
    AddTotalHours = Table.AddColumn(
        Source,
        "Total Hours",
        each if [Start] = null or [End] = null
            then null
            else Number.Round(Duration.TotalHours([End] - [Start]), 2),
        type number
    )
in
    AddTotalHours

    Please give kudos or mark it as resolved once confirmed.

    Regards,

    Praful

    • Dicken's avatar
      Dicken
      Icon for Post Prodigy rankPost Prodigy
      5 days ago

      That is pretty much the way i went about it.   

       

      let

      Adate = #datetime(2020, 1, 1, 13, 30, 00),

      Bdate = #datetime(2020, 1, 3, 14, 45, 00),

      timeunits = Bdate - Adate,

      hours_decimal = Number.Round(Number.From(timeunits) * 24, 10)

      in

      hours_decimal

       

       

      Total hours as time

       

      let

      adatetime = #datetime(2020, 1, 1, 13, 30, 00),

      bdatetime = #datetime(2020, 1, 3, 14, 45, 00),

      diff = bdatetime - adatetime,

      ddays = Duration.Days(diff) * 24,

      hhours = Duration.Hours(diff),

      thours = ddays + hhours,

      tmins = Duration.Minutes(diff),

      tsecs = Duration.Seconds(diff),

      aslist = {thours, tmins, tsecs},

      totext = List.Transform(aslist, (x) => Text.PadStart(Text.From(x), 2, "0"))

      in

      totext{0} & ":" & totext{1} & ":" & totext{2}

      let see if it let's me post this, could not find 'code tags' in  reply? 

  • Rupa01's avatar
    Rupa01
    Icon for Solution Sage rankSolution Sage
    14 days ago
     
    In Power Query, Go to Add Column -> Custom Column.
    Add below logic to calculate total hours between a Start Timestamp and End Timestamp
     
    Duration.TotalHours([End Time] - [Start Time])
     
    If you have only Start Timestamp and End Timestamp to be current day, the use below logic.
    You can also round up the result as per your requirement.
     
    Duration.TotalHours(DateTime.LocalNow() - [Start Time])
     
    💡 Helpful? Give a Kudos 👍 — keep the community growing
    ✅ Solved your issue? Mark as Solution ✔️ — help others find it faster

    Best regards,
    Rupasree Achari | BI & Fabric Analytics Engineer 
  • Murtaza_Ghafoor's avatar
    Murtaza_Ghafoor
    Icon for Super User rankSuper User
    14 days ago

    Dicken​ 
    You need these two fields in your table

    • StartDateTime
    • EndDateTime

    Go to Add Column → Custom Column and use:

    Duration.TotalHours([EndDateTime] - [StartDateTime])

    Example:

    StartDateTimeEndDateTimeTotal Hours
    29-Sep-2026 08:0029-Sep-2026 17:309.5
    29-Sep-2026 09:1530-Sep-2026 10:1525

     

    If this helps, ✓ Mark as Kudos | Help Other

     

     

  • pankajnamekar25's avatar
    pankajnamekar25
    Icon for Super User rankSuper User
    14 days ago

    Hello Dicken​ 

    To calculate total hours between two date/time columns, go to Add Column → Custom Column,

    Total Hours =

    Duration.TotalHours([EndDateTime] - [StartDateTime])

    Replace the column names with yours. Both columns should have the Date/Time type; set the result to Decimal Number.

    Example: 28 September at 08:00 → 29 September at 10:30 returns 26.5 hours.

    If you have only one date/time column, share an example value and your expected result.

     

    If my response helped you, please consider clicking
    Accept as Solution and giving it a Like – it helps others in the community too.

    Thanks,

    Connect with me on:
    LinkedIn |
    Data With Pankaj - YouTube

  • ShivekMaharaj's avatar
    ShivekMaharaj
    Icon for Community Champion rankCommunity Champion
    15 days ago

    Hi Dicken​,

    Could you please share a small example of the data and the result you are trying to produce?

    For example, it would help to know whether you have:

    StartDateTime

EndDateTime

    and want the elapsed time between them expressed as total hours, such as 2.5 hours.

    Also, are you looking to calculate this in DAX, Power Query, or as a calculated column/measure?

    If you can post 3-4 sample rows together with the expected Total Hours result, it should be possible to give you the correct expression without making assumptions.

    AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.

    • Dicken's avatar
      Dicken
      Icon for Post Prodigy rankPost Prodigy
      9 days ago

      No, it won't let me I did try posting both the xl solution and my power query
      verson, but it would not allow me to publish I just kept getting something aboiut 
      html tags, i tried both as text and code button.