Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
3.4k views
in Technique[技术] by (71.8m points)

excel - How can I get the Decimals in the following function in PowerQuery?

I have the following doubt, I have this function to calculate dates between 2 dates, however these dates have hours, I have been working on the function to get the decimals however I still dont get the decimals, I have tried ( Number.Round), (Decimal.From)

Please function below :

= (InitialDate as datetime, FinalDate as datetime ) as number => 
let
DaysBetweenDates = Duration.Days(FinalDate-InitialDate),
DaysList = List.DateTimes(List.Min({InitialDate,FinalDate}),Number.Abs(Decimal.From(DaysBetweenDates))+1.00, Duration.From(Decimal.From(2.00))),
WeekDaysList = List.Select(DaysList, each (Date.DayOfWeek(_, Day.Monday) < 5) ),
WorkingDays = (if DaysBetweenDates < 0.00 then -1.00 else 1.00) * List.Count(WeekDaysList)
in
WorkingDays

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

Assuming in your comment that your 0.80 days is incorrect (and should be 0.92 days), and that you are really looking for days and fractions of a day, you can use simply:

(InitialDate as datetime, FinalDate as datetime ) as number => 

let
    WorkingDays = Number.From(FinalDate) - Number.From(InitialDate)
in
    WorkingDays

and you could also use Number.Round if you want to round the result

eg:

Number.Round(Number.From(FinalDate) - Number.From(InitialDate),3)

If that is not what you want, you will need to be more specific.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...