Creating Date from Year & Month Values

[acool]

Hi Everyone,

I am currently trying to create a formula which provides a date output based on the year and month provided. In instances where I am provided year & month, I would like an output value that provides a value that illustrates the the first day of the month for that respective month & year. Attaching an image below for what I am trying to accomplish. In this instance I am provided column A & B, and would like to get to the output in Column C. Any help would be greatly appreciated. Thank You!

 

[Joe4]

Put this formula in cell C2 and format result as date:

=DATEVALUE(B2 & " 1, " & A2)

 

[acool]

Put this formula in cell C2 and format result as date:

=DATEVALUE(B2 & " 1, " & A2)

Hi Joe,

Thanks for your feedback. After trying this formula, I am still getting a value error in Cell C2. Not sure why it is giving me this error.

 

[Joe4]

Hi Joe,

Thanks for your feedback. After trying this formula, I am still getting a value error in Cell C2. Not sure why it is giving me this error.


View attachment 92375

I am guessing that you are probably using a European version of Excel then, right? It works in the US, but I believe there is a different default date format for the European settings. I will have to see if I can find out what that is.

 

[Joe4]

Does this one work?

=DATEVALUE("01-" & B2 & "-" & A2)

 

[acool]

I am guessing that you are probably using a European version of Excel then, right? It works in the US, but I believe there is a different default date format for the European

Does this one work?

=DATEVALUE("01-" & B2 & "-" & A2)

This did the trick! What is the difference in the formula? Why does one work vs. the other? Curious to understand.

 

[Joe4]

The difference, as I mentioned previously is in the default regional settings for your version of Excel.
Here in the US, "May 1, 2021" is recognized as an acceptable date format.
But it apparently doesn't work on your version.
So I tried "01-May-2021" (I thought I had seen that at one time for European versions), and it seems to have worked.

Also, when marking posts as the solution, please mark the initial post that contains the solution (not your own post acknowledging that some other post contains the solution).
I have updated this for you.

 

[acool]

The difference, as I mentioned previously is in the default regional settings for your version of Excel.
Here in the US, "May 1, 2021" is recognized as an acceptable date format.
But it apparently doesn't work on your version.
So I tried "01-May-2021" (I thought I had seen that at one time for European versions), and it seems to have worked.

Also, when marking posts as the solution, please mark the initial post that contains the solution (not your own post acknowledging that some other post contains the solution).
I have updated this for you.

Ok, thank you