Forum Discussion

miguelflores's avatar
miguelflores
Icon for Advocate I rankAdvocate I
9 years ago
Solved

How to get month in a date

Hi!   I'm encountering a road block, how do i get the month in a date data type? I have a column date hired and regularization date. I want to compute for the 5, 3 and 1 month remaining before reg...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

    Please try this

     

    Months Between = IFERROR(DATEDIFF(
     [date_hired],
     [regularization_date],
     MONTH
     ),-1)
miguelflores's avatar
miguelflores
Icon for Advocate I rankAdvocate I
9 years ago

Hi Phil,

 

This is an example of my data.

 

My goal is to get how many months remaining before regularization.

 

Thanks,

Phil_Seamark's avatar
Phil_Seamark
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

Hi miguelflores,

 

It looks like your columns are using a DATE datatype whcih is good.

 

If you are just after the difference between the two dates on each row you can add a calculated column to your table along these lines.  This will count the number of months between the two dates.  Please let me know if this is now what you are after.

 

Months Between = DATEDIFF(
                        [date_hired],
                        [regularization_date],
                        MONTH
                        )
  • miguelflores's avatar
    miguelflores
    Icon for Advocate I rankAdvocate I
    9 years ago

    Hi Phil,

     

    Yes, now I'm after the number of months.

    I tried the formula and i'm encountering an error:

     

    I also tried switching the regularization_date and hired_date

    Months Between = DATEDIFF([date_hired],[regularization_date],MONTH)
    Months Between = DATEDIFF([regularization_date],[date_hired]MONTH)

     

    Thanks!

    • Phil_Seamark's avatar
      Phil_Seamark
      Icon for Microsoft Employee rankMicrosoft Employee
      9 years ago

      Please try this

       

      Months Between = IFERROR(DATEDIFF(
       [date_hired],
       [regularization_date],
       MONTH
       ),-1)
      • miguelflores's avatar
        miguelflores
        Icon for Advocate I rankAdvocate I
        9 years ago

        Thanks Phil! I got it! haha