Average of range of dates.

[TheeWoko]

I have a column of dates as text. some are ranges. I want to use the average of the ranges as the final date while keeping the non-ranges before converting all to int so I can do some more work on them. I've tried extracting the ranges as text so I can subsequently average them, didn't work.

[MohamedFowzan1 · Accepted solution]

Hi @TheeWoko 

Use the below logic to checksif the text contains a hyphen ("-"), splits the range, calculates the average, and returns it as a number.

If no range, it converts the text directly to number.
New custom column with MCode:

// Custom column logic
let
    DateText = [Date], // Replace 'Date' with your actual column name
    HasRange = Text.Contains(DateText, "-"),
    Result =
        if HasRange then
            let
                Parts = Text.Split(DateText, "-"),
                StartNum = Number.FromText(Parts{0}),
                EndNum = Number.FromText(Parts{1}),
                AvgNum = Number.Round((StartNum + EndNum) / 2, 0)
            in
                AvgNum
        else
            Number.FromText(DateText)
in
    Result

[MattAllington]

I can only see ranges. It's hard to give solid guidance unless the sample data is typical. I would be doing something like

load the data into Power Query

split column on the delimiter "-" to create 2 columns 

convert to integer

add a new column to do your average calculation (add custom column, then do the calc to get the average)