Power Query dates

[Dicken]

  so power  query can handle a pre 1900 date for calculation,  but  recently found if you have a date in excel 
of DATE( 1900,1,1)  , load to pq , then back to excel it becomes,   00/01/1900 ,  if you create in pq it's ok.
apparently there is a fake  leap year i  1900 /  02 / 29  in excel , so when loaded to power query each date = day -1, 
until the 'fake'  29 is met and they converge. 
Does anyone have any advice or comments on working with dates prior to this spot or 1900 ? 

Richard. 

[AmiraBedh · Accepted solution]

Hello Dicken !

Thank you for posting on MS Fabric Community.

What you see is a real and very old Excel limitation but not a PQ mistake because Excel default 1900 date system treats 1900 as if it were a leap year so it includes the non existent date 1900-02-29 for compatibility reasons. Microsoft documents this behavior and says it is intentionally preserved because changing it would break existing workbooks. https://learn.microsoft.com/en-us/troubleshoot/microsoft-365-apps/excel/wrongly-assumes-1900-is-leap...

My advice to you is do not round trip sensitive early dates through Excel date cells if they are before 1900-03-01  and especially if they are before 900, you need to leep them in PQ or the data model or another system as long as possible. You can store them as text or as separate year/month/day fields when the final destination is an Excel sheet and that way you avoid Excel converting them into its serial date system.

The 1904 date system is not really a fix here. It changes the starting point, but it does not make Excel a good engine for historical dates before 1900.

https://support.microsoft.com/en-us/office/date-systems-in-excel-e7fe7167-48a9-4b96-bb53-5612a800b48...

[AmiraBedh · Accepted solution]

I think your main issue is in this part : 

Table.TransformColumnTypes(atable, {{s, type date}, {e, type date}})

because I suppose if the source values are text like dd/MM/yyyy,  PQ will parse them using the current locale unless you explicitly control it.

You can try : 

(atable as table, s as text, e as text) as table =>
let
Converted =
Table.TransformColumns(
atable,
{
{s, each Date.From(_), type date},
{e, each Date.From(_), type date}
}
),
AddedDifference =
Table.AddColumn(
Converted,
"difference",
each Duration.Days(Record.Field(_, e) - Record.Field(_, s)),
Int64.Type
),
BackToText =
Table.TransformColumns(
AddedDifference,
{
{s, each Date.ToText(_, "dd/MMM/yyyy"), type text},
{e, each Date.ToText(_, "dd/MMM/yyyy"), type text}
}
)
in
BackToText

[Dicken]

Thanks to all who replied, i'll workd through each  in time. 

[v-sgandrathi]

Hi @Dicken,

Thank you @Nasif_Azam @AmiraBedh for your answers to the query.

Following up to check whether you got a chance to review the suggestions given. If the issue still persists please let us know. Glad to help. 

Thank you.

[Nasif_Azam · Accepted solution]

Hey @Dicken ,

Nice pattern, the text → date → calc → text sandwich is exactly the right way to keep Excel's 1900 bug out of the picture, since M code never hands a serial back to Excel.

A few small refinements worth folding in:

1. Lock the culture on parsing: The type date uses ambient locale, so 03/01/1850 silently flips between US and UK. Use Date.FromText(_, [Format="dd/MM/yyyy", Culture="en-GB"])

2. Use Duration.Days(e - s) instead of Number.From subtraction same result, clearer intent, no reliance on the OLE epoch.

3. Typo on the output formats: "dd/MMM/yyyy" vs "dd/MMMM/yyyy" the two columns will render differently.

4. Pin culture onDate.ToText too, or month names break on non‑en gateways.

5. Wrap in try ... otherwise null so one bad/blank row doesn't kill the refresh.

A revised version you should try. Same spirit, a bit more defensive, and generalised so you can reuse it across different columns and formats:

(atable as table, s as text, e as text,
 optional inFormat as text, optional outFormat as text,
 optional culture as text, optional diffCol as text) =>
let
    inFmt   = inFormat  ?? "dd/MM/yyyy",
    outFmt  = outFormat ?? "dd/MMM/yyyy",
    cult    = culture   ?? "en-GB",
    diffName = diffCol  ?? "difference",

    // 1. Parse text -> date explicitly (locale-safe, error-tolerant)
    parsed = Table.TransformColumns(
        atable,
        {
            {s, each try Date.FromText(_, [Format = inFmt, Culture = cult]) otherwise null, type date},
            {e, each try Date.FromText(_, [Format = inFmt, Culture = cult]) otherwise null, type date}
        }
    ),

    // 2. Add the day difference using Duration.Days
    withDiff = Table.AddColumn(
        parsed,
        diffName,
        each
            let a = Record.Field(_, s), b = Record.Field(_, e) in
            if a = null or b = null then null
            else Duration.Days(b - a),
        Int64.Type
    ),

    // 3. Convert both dates back to text with a single consistent format
    back = Table.TransformColumns(
        withDiff,
        {
            {s, each if _ = null then null else Date.ToText(_, [Format = outFmt, Culture = cult]), type text},
            {e, each if _ = null then null else Date.ToText(_, [Format = outFmt, Culture = cult]), type text}
        }
    )
in
    back

Called like:

= fnDateDiff(Source, "Birth", "Death")                       // defaults
= fnDateDiff(Source, "Birth", "Death", "yyyy-MM-dd", "dd/MMM/yyyy", "en-GB", "AgeDays")

I have tested this pattern against dates like #date(1642,12,25) and #date(1727,3,20) using Duration.Days returns the correct interval, the round‑trip to text and back is clean, and because Excel only ever sees strings like 25/Dec/1642, the 1900 serial bug never enters the picture.

One small caveat to keep in mind: once the column lands in Excel as text, users lose Excel's native date filter / slicer behaviour on it. If that matters for a given report, you can add a sibling sort‑key column like [Year]*10000 + [Month]*100 + [Day] before the text conversion and sorts chronologically even for BC dates if you ever go that far back.

Best Regards,
Nasif Azam

[Dicken]

did make this up to add a   column  for  calculating dates between  that are text then  so to date 
, calculate, back to text for loading, 

(atable as table, s as text, e as text) =>
  let
    tr = Table.TransformColumns(
      Table.AddColumn(
        Table.TransformColumnTypes(atable, {{s, type date}, {e, type date}}),
        "difference",
        each Number.From(Record.Field(_, e)) - Number.From(Record.Field(_, s)),
        Int64.Type
      ),
      {
        {s , each Date.ToText(_, [Format = "dd/MMM/yyyy"])},
        {e, each Date.ToText(_, [Format = "dd/MMMM/yyyy"])}
      }
    )
  in
    tr

[AmiraBedh · Accepted solution]

I think your main issue is in this part : 

Table.TransformColumnTypes(atable, {{s, type date}, {e, type date}})

because I suppose if the source values are text like dd/MM/yyyy,  PQ will parse them using the current locale unless you explicitly control it.

You can try : 

(atable as table, s as text, e as text) as table =>
let
Converted =
Table.TransformColumns(
atable,
{
{s, each Date.From(_), type date},
{e, each Date.From(_), type date}
}
),
AddedDifference =
Table.AddColumn(
Converted,
"difference",
each Duration.Days(Record.Field(_, e) - Record.Field(_, s)),
Int64.Type
),
BackToText =
Table.TransformColumns(
AddedDifference,
{
{s, each Date.ToText(_, "dd/MMM/yyyy"), type text},
{e, each Date.ToText(_, "dd/MMM/yyyy"), type text}
}
)
in
BackToText

[AmiraBedh · Accepted solution]

Hello Dicken !

Thank you for posting on MS Fabric Community.

What you see is a real and very old Excel limitation but not a PQ mistake because Excel default 1900 date system treats 1900 as if it were a leap year so it includes the non existent date 1900-02-29 for compatibility reasons. Microsoft documents this behavior and says it is intentionally preserved because changing it would break existing workbooks. https://learn.microsoft.com/en-us/troubleshoot/microsoft-365-apps/excel/wrongly-assumes-1900-is-leap...

My advice to you is do not round trip sensitive early dates through Excel date cells if they are before 1900-03-01  and especially if they are before 900, you need to leep them in PQ or the data model or another system as long as possible. You can store them as text or as separate year/month/day fields when the final destination is an Excel sheet and that way you avoid Excel converting them into its serial date system.

The 1904 date system is not really a fix here. It changes the starting point, but it does not make Excel a good engine for historical dates before 1900.

https://support.microsoft.com/en-us/office/date-systems-in-excel-e7fe7167-48a9-4b96-bb53-5612a800b48...