Modern spreadsheet functions

[taviso]

I've started maintaining a wiki page on how to emulate modern functions in 1-2-3.

For example, there is no @WEEKDAY function in 1-2-3, but that doesn't mean you can't calculate it!

https://github.com/taviso/123elf/wiki/Functions#weekday

Any suggestions welcome!

[rruhle]

@EOMONTH is another modern function missing from 123

My method requires a few steps.

Get @day(#) of @Date(Y,M,D) equal to first day of next month minus 1 day.

@day(@Date(@year(@now),@month(@now)+1,1)-1)

Since this formula chokes in December, fix December error:

EOMmonth = @Date(@year(@now),@month(@now)+@if(@month(@now)>11,-1,+1),1)-1

Finally Format cell to preferred date format

[taviso]

Ah-ha, thanks! I'll add it to the wiki...

Hmm, I wonder if we can generalize it to any month offset... although maybe negative is hard, let me experiment!

[taviso]

It's very ugly, but this does seem to work:

	A	B	C
1	Start Date:	01-Jan-2011
2	Month Offset:	-16
3	Result:	30-Sep-2009

B3 Contains:

@DATE(@YEAR(B1)+@INT((B2+1)/12)+(@MONTH(B1)+@MOD(B2+1,12)>12)-(@MONTH(B1)+@MOD(B2+1,12)<1),((B2+1<0)
*12+@MOD(@MONTH(B1)+B2+1,12)),1)-1

[taviso]

I realized a much nicer formula on the way to work this morning, just convert everything to months:

@DATE(@YEAR(B1)+(@MONTH(B1)+B2)/12,@MOD(@YEAR(B1)*12+@MONTH(B1)+B2,12)+1,1)-1

B1 = Start Date
B2 = Month offset

[rruhle]

Looks good from my house.

[rruhle]

Thanks to Taviso for this project, regardless of minor issues between versions and limited community support, from those of us determined to use this platform with Gratitude.