Converting a complex Excel formula to M language

If I had more time, I'd optimize the heck out of this because the Excel formula in the USED field is a bit redundant in some places, plus it cannot do what PQ can do. However, I pretty much replicated it, and it works. Same results as Used.

let
    Source = Excel.CurrentWorkbook(),
    Tableau1 = Source{[Name="Tableau1"]}[Content],
    #"Type modifié" = Table.TransformColumnTypes(Tableau1,{{"Date", type date}, {"Entry date", type date}, {"Leaving date", type date}}),
    #"Personnalisée ajoutée" = Table.AddColumn(#"Type modifié", "New used", each if [Leaving date] = null and
[Entry date] <= Date.From(#date(Date.Year([Date]), Date.Month([Date]), 15))
then 1
else if [Leaving date] <> null and [Entry date] >= Date.From(#date(Date.Year([Date]), Date.Month([Date]), 15))
then 0
else if [Leaving date] <> null and [Leaving date] >= Date.From(#date(Date.Year([Date]), Date.Month([Date]), 15))
then 1
else 0),
    NewValue = 
        Table.AddColumn(
            #"Personnalisée ajoutée",
            "NewValue",
            each
                let
                    varPeriodYear = Number.IntegerDivide([Period],100),
                    varPeriodMonth = [Period] - varPeriodYear * 100
                in
                if [Departure year] = ""
                then 
                    if [Arrival year] <> varPeriodYear or [Arrival month] <> varPeriodMonth 
                        then
                            if [Contract type] = "Expatriate" and [Registered] = 1
                            then 0
                            else 1
                        else
                            if [Arrival day] > 15
                            then 0
                            else 
                                if [Contract type] = "Expatriate" and [Registered] = 1
                                then 0
                                else 1
                    else
                        if Duration.TotalDays(#date([Departure year], [Departure month], [Departure day]) - #date([Arrival year], [Arrival month], [Arrival day])) < 15
                        then 0
                        else 
                            if [Departure month] <> varPeriodMonth
                            then 0
                            else 
                                if [Departure day] < 15
                                then 0
                                else
                                    if [Contract type] = "Expatriate" and [Registered] = 1
                                    then 0
                                    else 1
			),
    #"Added Custom" = Table.AddColumn(NewValue, "Custom", each [NewValue] = [Used])
in
    #"Added Custom"

You can see that the final column returns TRUE because the New Value column matches the Used column.

Spigaw that is a massive "it depends" and would require untangling the original code above and inserting it in the right place. As I said in my original reply, that was not optimized because the excel formula had some nested IF() statements that were redundant, but it would take time to map it out and ensure it was working right. It was spaghetti code to begin with, and this makes it worse. 😉