Dear Team
I am trying to calculate total times record open from Reported Date up to current date.
I will add new column called Total Duration where it needs to calculate DD / MM / YYYY , For example 1 Day , 15 Hrs , 20 Minutes
Present Day - Reported Date = 1 Day , 15 Hrs, 20 Minutes this is the time ticket been open as its present day
How can I do that?
Your Reported Dates are not actually dates but formatted as General so they cannot be used in formulas. You need to convert them to usable formats. Read this for an explanation of how to this.
https://exceljet.net/formula/convert-date-string-to-date-time
Can be accomplished with Power Query/Get and Transform Data
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Reported Date", type datetime}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each DateTime.LocalNow() - DateTime.From([Reported Date])),
#"Inserted Days" = Table.AddColumn(#"Added Custom", "Days", each Duration.Days([Custom]), Int64.Type),
#"Inserted Hours" = Table.AddColumn(#"Inserted Days", "Hours", each Duration.Hours([Custom]), Int64.Type),
#"Inserted Minutes" = Table.AddColumn(#"Inserted Hours", "Minutes", each Duration.Minutes([Custom]), Int64.Type),
#"Added Suffix" = Table.TransformColumns(#"Inserted Minutes", {{"Days", each Text.From(_, "en-US") & " Days", type text}}),
#"Added Suffix1" = Table.TransformColumns(#"Added Suffix", {{"Hours", each Text.From(_, "en-US") & " Hours", type text}}),
#"Added Suffix2" = Table.TransformColumns(#"Added Suffix1", {{"Minutes", each Text.From(_, "en-US") & " Minutes", type text}}),
#"Merged Columns" = Table.CombineColumns(#"Added Suffix2",{"Days", "Hours", "Minutes"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Duration"),
#"Removed Columns" = Table.RemoveColumns(#"Merged Columns",{"Custom"})
in
#"Removed Columns"File attached for review. Note that you have approx 40 rows with no reporting date. How would you like those handled?
Hi
There should be reported date, I will check data why it is not there, but If I want to paste new data it will not work, do I need to do something after pasting new data?
Also why in the raw data there is reported date for all data but in the second table half of reported date not showing up ?
In the PQ solution, if you add new data, click on the Refresh All and your PQ will update automatically
Don’t have an account yet? Register yourself now and be a part of our community!