Month/Year from Week/Year

[DowReena]

Is there a way to return Month/Year from a cell that contains Week/Year?

Example: A1 contains "03/2016" and would like to show "January/2016" or "01/2016"

 

[Llupo01]

Hello DowReena

can you try this?

ActiveCell.NumberFormat = "MMMMMMMMM/yyyy"

or

ActiveCell.NumberFormat = "mm/yyyy"

regards,
Tom

 

[StephenCrump]

Welcome to the Forum!

Is there a way to return Month/Year from a cell that contains Week/Year?

Yes, but you'll need to let us know your numbering system, e.g. ISO, or perhaps something different?

How are you determining Week 1?

 

[DowReena]

Hello DowReena

can you try this?

ActiveCell.NumberFormat = "MMMMMMMMM/yyyy"

or

ActiveCell.NumberFormat = "mm/yyyy"

regards,
Tom

Hi-sorry if this is a dumb question..but where do I input that code? Pasting that in B1 does not do anything..

 

[DowReena]

Is it based on Monday - Sunday timing. So Week 1 of 2016 would be Jan 4 - Jan 10.

The rest are here: https://www.epochconverter.com/weeks/2016

 

[Jonmo1]

Try

=TEXT(WORKDAY.INTL(DATE(RIGHT(A1,4),1,0),LEFT(A1,2),"0111111"),"mmmm/yyyy")

 

[DowReena]

Try

=TEXT(WORKDAY.INTL(DATE(RIGHT(A1,4),1,0),LEFT(A1,2),"0111111"),"mmmm/yyyy")

Yes-it worked! Thank you so much!

 

[Jonmo1]

You're welcome.

 

[StephenCrump]

Try

=TEXT(WORKDAY.INTL(DATE(RIGHT(A1,4),1,0),LEFT(A1,2),"0111111"),"mmmm/yyyy")

That's a neat formula, if the first week always starts on the first Monday of the year.

But if DowReena is using ISO-8601 as indicated, there is the additional requirement that Week 1 must contain the first Thursday of the year. So, for example, Week 1 2015 actually starts on Monday 29 December 2014.

B1: =DATE(RIGHT(A1,4),1,-2)-WEEKDAY(DATE(RIGHT(A1,4),1,3))+7*LEFT(A1,2)
C1: =TEXT(DATE(RIGHT(A1,4),1,-2)-WEEKDAY(DATE(RIGHT(A1,4),1,3))+7*LEFT(A1,2),"mmm yyyy")

Sheet1

*	A	B	C

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: center"]01/2015[/TD]
[TD="align: center"]Monday 29 Dec 2014[/TD]
[TD="align: center"]Dec 2014[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: center"]05/2011[/TD]
[TD="align: center"]Monday 31 Jan 2011[/TD]
[TD="align: center"]Jan 2011[/TD]

</tbody>

DowReena, ISO-8601 doesn't provide any correspondence between week number and month. Jonmo1's and my formula give you the month of the Monday. So the second example above shows as January 2011, although 6 of the 7 days actually fall into February.