Are there are any reported problems with Excel formulas in Beta, particularly with the YEAR function or VSTACK and HSTACK?

Question

Are there are any reported problems with Excel formulas in Beta, particularly with the YEAR function or VSTACK and HSTACK? I have been attempting to follow a YouTube video "How to make a CALENDAR formula in excel | LAMBDA" created by Karina Adcock. I have reached the following stage. The following formula returns 1905 instead of *2023* or any chosen year. I am using Office 365 (Beta). I am receiving no error messages. I have spent hours working through all the suggestions from Copilot without success. In the end, even Copilot was stumped and referred me here. The dreaded formula is...

=VSTACK(HSTACK(TEXT(Start,"mmmm"),YEAR(Start)),

{"Mon","Tue","Wed","Thu","Fri","Sat","Sun"},

LET(first,EOMONTH(Start,-1)+1,

seq,SEQUENCE(6,7,first-WEEKDAY(first,3),1),

IF(MONTH(seq)=MONTH(Start),TEXT(seq,"D"),"")))

where Start is a starting date calculated from a Data Validation calculated list.

The formula used successfully by Karina in her YouTube video is...

=VSTACK(HSTACK(TEXT(B2,"mmmm"),YEAR(B2)),

{"Mon","Tue","Wed","Thu","Fri","Sat","Sun"},

LET(first,EOMONTH(B2,-1)+1,

seq,SEQUENCE(6,7,first-WEEKDAY(first,3),1),

IF(MONTH(seq)=MONTH(B2),TEXT(seq,"d"),"")))

where B2 is her chosen starting date (unnamed) 12-Jul-2023

Edit.

The problem only manifested when attempting to add the HSTACK(TEXT(Start,"mmmm"),YEAR(Start)) portion of the formula. No problems prior to that. Some of the suggestions from Copilot was to test the serial number of my calculated start date (correct), trying to use a non calculated date in a different cell and even hard coding a year into the formula. None of this made a difference. It keeps returning 1905.

Answer 1

Thank you for your understanding. I have reported this issue in the feedback portal, hope they will notice and fix this.

Answer 2

Hi Thomas.

I am aware that occasionally bugs might appear in Beta. After all, that's what it's there for. I am perfectly happy to retain Beta. I'm not going to let one little mystery that's not causing me any problems with anything else put me off and miss out on all the other benefits. The issue I experienced isn't even that much of a problem for me, as I was able to complete the exercise, missing out this stage. Perhaps a future update might rectify the bug.

Thanks for your time anyway.

Regds

Ian

Answer 3

Hi Ian,

I am not sure if you revert back to mainstream version will still be able to use these functions. It's common for beta channel products having some mysterious bugs. We haven't received more reports about this. As we are not responsible for development nor dev team will monitor the community, I recommend to report a feedback through File > Feedback, dev team watch there.

Best Regards,

Thomas.L - MSFT | Microsoft Community Support Specialist

Answer 4

Hi Thomas,

Thanks for getting back to me.

I currently running Version 2407 (Build 17730.20000 Click-to-Run) Beta Channel

I tried to include a screenshot with my original post but upload kept failing. I just succeeded.

Answer 5

Hi Ian,

This is what I tested on my computer, seems everything good.

May I ask the version you are using? This is the version what I am using:

Best Regards,

Thomas.L - MSFT | Microsoft Community Support Specialist