topic combine formula in Discussions

combine formula

joann — Fri, 09 Jun 2023 22:08:07 GMT

Hi, could someone help me combine the 2 formulas below? The second one is simply minus the time of day part. Thank you.

----------------------------------------

New Column( "week start",
Numeric,
"Nominal",
Format( "m/d/y", 12 ),
Input Format( "m/d/y" ),
Formula(
If( Day Of Week( :WR_Date ) == 1,
(:WR_Date - In Days( Day Of Week( :WR_Date ) )) - In Days( 5 ),
(:WR_Date - In Days( Day Of Week( :WR_Date ) )) + In Days( 2 )
)
),
Set Selected
),
New Column( "Date[week start]",
Numeric,
"Nominal",
Format( "m/d/y", 12 ),
Input Format( "m/d/y" ),
Formula( :week start - Time Of Day( :week start ) ),
Set Selected
),

Re: combine formula

Dan_Obermiller — Thu, 11 Mar 2021 01:31:58 GMT

I'm doing this quickly, but I think this would work:

New Column( "Date[week start]",
Numeric,
"Nominal",
Format( "m/d/y", 12 ),
Input Format( "m/d/y" ),
Formula( ( If( Day Of Week( :WR_Date ) == 1,
(:WR_Date - In Days( Day Of Week( :WR_Date ) )) - In Days( 5 ),
(:WR_Date - In Days( Day Of Week( :WR_Date ) )) + In Days( 2 )
) )
 - 
Time Of Day( ( If( Day Of Week( :WR_Date ) == 1,
(:WR_Date - In Days( Day Of Week( :WR_Date ) )) - In Days( 5 ),
(:WR_Date - In Days( Day Of Week( :WR_Date ) )) + In Days( 2 )
) ) ) ) )

All I did is copied the first formula (for "week start") and replaced every reference to "week start" in the second formula with the copied information.

I added some extra spaces to (hopefully) see the formula a little bit easier.

Also note that I did absolutely no testing to ensure this is correct.

Re: combine formula

jthi — Thu, 11 Mar 2021 09:13:20 GMT

I think you could also directly remove Time Of Day(:WR_Date) because the result of that if statement should have same hours, minutes and seconds as the :WR_Date column

Formula(
	If(Day Of Week(:WR_Date) == 1,
		(:WR_Date - In Days(Day Of Week(:WR_Date))) - In Days(5),
		(:WR_Date - In Days(Day Of Week(:WR_Date))) + In Days(2)
	) - Time Of Day(:WR_Date)
),

Re: combine formula

joann — Thu, 11 Mar 2021 16:39:55 GMT

Thank you Dan! This works!

Re: combine formula

joann — Thu, 11 Mar 2021 16:40:15 GMT

Thank you Jarmo! This works!