Days into months and remaining days conversion

[niyati_61]

Hello all,

How does one convert days into months and remaining days?

Eg: Days = 178, 

Converted to Months = 5 months and 26 days

[v-zhangti]

Hi, @niyati_61 

You can try the following methods.

1. Use IF function to determine the size of the date.

DAYS =
IF ( [PLAN] > [DATE TODAY], [PLAN] - [DATE TODAY], [DATE TODAY] - [PLAN] )

  

   2. Create a new column to change the number of days to the month plus the number of days.

Column = 
INT ( [DAYS] / 30 ) & "months"
    & (
        [DAYS]
            - INT ( [DAYS] / 30 ) * 30
) & "days"

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more.

[AlexisOlson]

This is very similar to what I initially suggested but months don't consistently have 30 days.

[niyati_61]

Alright, that makes sense. 

Okay, but what I'm doing is, I'm subtracting today's date from the Planned date (As seen in the image). 

When I subtract the 2 dates in the Query Editor, I get the Number of days. 

Eg: xx/xx/2021 - 31/10/2021 = -178 Days

Now, how do I convert this 178 Days into Months and remaining days? Because just 178 days doesn't make sense.

[AlexisOlson · Accepted solution]

I can't think of a very elegant way to do this but this should work.

Define two new custom columns:

MonthDiff =
if Date.Day([Today]) >= Date.Day([Plan])
then 12 * (Date.Year([Today]) - Date.Year([Plan])) + Date.Month([Today]) - Date.Month([Plan])
else 12 * (Date.Year([Today]) - Date.Year([Plan])) + Date.Month([Today]) - Date.Month([Plan]) - 1

RemainderDayDiff = Duration.Days(Date.AddMonths([Today], -[MonthDiff]) - [Plan])

[jhenderson525]

Hi @AlexisOlson. How would you revise this formula to handle blanks that may occur in the Planned column?

[AlexisOlson]

Assuming you want blanks for the result, try this:

MonthDiff =
if [Plan] = null then null
else if Date.Day([Today]) >= Date.Day([Plan])
then 12 * (Date.Year([Today]) - Date.Year([Plan])) + Date.Month([Today]) - Date.Month([Plan])
else 12 * (Date.Year([Today]) - Date.Year([Plan])) + Date.Month([Today]) - Date.Month([Plan]) - 1

 If not, then replace the second "null" with whatever you do want the logic to be when [Plan] is blank.

[jhenderson525]

@AlexisOlson thank you sir. this is perfect.

[AlexisOlson]

You can't without more information (like month and day of month) unless you assume that months have a fixed number of days.

If you assume months are 30 days, then you can calculate 178/30 = 5.933 and just take the 5 part and then the remainder 178 - 5 * 30 = 178 - 150 = 28.

Months = INT ( 178 / 30 )
Days = MOD ( 178, 30 )