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)
Phil_Seamark's avatar
Phil_Seamark
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

Hi miguelflores,

 

If I understand your question correctly, you have a column that represents months and you can't convert this to a data type of Date.

 

Try making it a single day of the month (include day month and year).  I suggest making it the 1st of the month.

 

Do you have an example of the data that won't convert?

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)