topic Re: How do I script to change the name of my columns, presently dates, to just the day? in Discussions

How do I script to change the name of my columns, presently dates, to just the day?

fionaweston — Sat, 10 Jun 2023 23:28:53 GMT

I want to change just the numeric continuous columns in my sheet which are presently full dates and are in order from 1st to 31st to just the day

ex 03/01/2020 to 1

03/02/2020 to 2

I would also like to add a column to my sheet with the Abbv Name of the Month.

Thanks in advance

Re: How do I script to change the name of my columns, presently dates, to just the day?

Georg — Mon, 19 Apr 2021 21:10:08 GMT

You could do it like this:

Names Default To Here( 1 );

dt = New Table( "Dates",
	New Column( "date", "Numeric", "Continuous", Format( "Date Abbrev", 20 ), set values( Date DMY( Index( 1, 31 ), 3, 2020 ) ) )
);
Wait( 3 );

dt << New Column( "Day", Formula( Day( :date ) ) );
dt:date << hide();
dt << New Column( "Month",
	Formula(
		Match( Month( :date ),
			1, "Jan",
			2, "Feb",
			3, "Mar",
			4, "Apr",
			5, "May",
			6, "Jun",
			7, "Jul",
			8, "Aug",
			9, "Sep",
			10, "Oct",
			11, "Nov",
			12, "Dec"
		)
	)
);

Re: How do I script to change the name of my columns, presently dates, to just the day?

txnelson — Mon, 19 Apr 2021 21:17:08 GMT

Here is how I would solve your request

Names Default To Here( 1 );
dt = Current Data Table();

theMonth=.;
For( i = 1, i <= N Cols( dt ), i++,
	If( Substr( Column( dt, i ) << get name, 1, 2 ) == "N(",
		If( isMissing(theMonth) == 1,
			theMonth = Month( Informat( Word( 2, Column( dt, i ) << get name, "()" ), "m/d/y" ) );
		);
		Column( dt, i ) << set name(
			Char( Day( Informat( Word( 2, Column( dt, i ) << get name, "()" ), "m/d/y" ) ) )
		)
	)
);

monthList = {"Jan","Feb","Mar","Apr","May","Jun",
	"Jul","Aug","Sep","Oct","Nov","Dec"
};
dt << New Column( monthList[theMonth]);

Re: How do I script to change the name of my columns, presently dates, to just the day?

fionaweston — Mon, 19 Apr 2021 21:23:52 GMT

Thanks Jim, this is great.