topic Re: bin each row based on date time in Discussions

bin each row based on date time

wencun6 — Sat, 10 Jun 2023 23:16:40 GMT

I often need to bin each row based on the date time. However, this is kind of troublesome for the data time. let say I am binning based before 7/22/2020 12:00:00pm as Good and after as bad. I can use the IF formula logic to compare the date time to 7/22/2020 12:00:00pm. But to do that I need to convert to the numeric value of 3678264000. if there any quicker way to do this? thanks

Re: bin each row based on date time

txnelson — Wed, 22 Jul 2020 20:12:26 GMT

what you need to use is the Informat() function

if( :date < informat("01/22/2020","m/d/y" ), "Good", "Bad");

Re: bin each row based on date time

Thierry_S — Wed, 22 Jul 2020 20:16:14 GMT

Hi,

Have you considered the "Informat" function, such as used below:

If( :Column 1 < Informat( "07/21/2020 12:00 PM", "MM/dd/yyyy hr:min" ),
	1,
	0
)

Best,

Re: bin each row based on date time

wencun6 — Wed, 22 Jul 2020 20:26:42 GMT

many thanks both. The informat works perfectly. :)

Re: bin each row based on date time

Thierry_S — Wed, 22 Jul 2020 22:11:41 GMT

Hi,
If you don't mind, could you select our answers as "Solutions": it will let other users know that this question has been addressed
Best,
TS

Re: bin each row based on date time

Jeff_Perkinson — Wed, 22 Jul 2020 22:40:07 GMT

You can use a date literal in JSL or the formula editor. You just need to use the ddMONyyyy format.

date=22Jul2020;

tomorrow=22Jul2020+in days(1);

show(date, tomorrow, format(tomorrow, "mm/dd/yyyy"));

in the log you get:

date = 22Jul2020;
tomorrow = 3678307200;
Format(tomorrow, "mm/dd/yyyy") = "07/23/2020";