TEXT() won't remove seconds

AppSheet AppSheet Q&A
1

I have written an IFS expression and it’s doing what I want it to do but in one of the IFS scenarios I need to remove seconds and no matter where I place the TEXT() function it won’t remove seconds, or at least not the way I want it to.

If I wrap the CONCATENATE () function in TEXT() it doesn’t remove seconds.

If I place it inside the CONCATENATE () function it doesn’t remove seconds.

If I wrap each of the ANY(SELECT()) in TEXT () it does remove seconds but it also displays time in AM-PM format (I need it to be 24h format).

Could anybody point me in the right direction?

(thank you for all the amazing posts and articles. I was able to write this and many other expressions by myself just by reading tons here and learning from this community)

here’s my expression(posting from smartphone, sorry about the crappy format and indentation and/or lack thereof)

IFS(
AND(
ISBLANK([1dallealle]),
ISBLANK(
ANY(
SELECT(Registro presenze[Data],
AND(
DAY([Data])=“1”,
YEAR([Data])=YEAR(TODAY()-5),
([Tirocinante] = [_THISROW].[Tirocinante]),
MONTH([Data])=MONTH(TODAY() - 5)),
FALSE
)
)
)
)
,

“------------”,
AND(OR(ISBLANK([1dallealle]),[1dallealle]=“-”),
ISNOTBLANK(
ANY(
SELECT(Registro presenze[Timestamp],
AND(
DAY([Data])=“1”,
YEAR([Data])=YEAR(TODAY()-5),
([Tirocinante] = [_THISROW].[Tirocinante]),
MONTH([Data])=MONTH(TODAY() - 5),
[Assenza]=“Assente”),
FALSE
)
)
)
)
,
“Assente”,

OR(ISBLANK([1dallealle]), [1dallealle] =“-”), TEXT(
CONCATENATE(

ANY(
SELECT(Registro presenze[Entrata],
AND(
DAY([Data])=“1”,
YEAR([Data])=YEAR(TODAY()-5),
([Tirocinante] = [_THISROW].[Tirocinante]),
MONTH([Data])=MONTH(TODAY() - 5)),
FALSE
)

),
“-”,

ANY(
SELECT(Registro presenze[Uscita],
AND(
DAY([Data])=“1”,
YEAR([Data])=YEAR(TODAY()-5),
([Tirocinante] = [_THISROW].[Tirocinante]),
MONTH([Data])=MONTH(TODAY() - 5)),
FALSE
)
)
)

)
,

AND(ISNOTBLANK([1dallealle]), [1dallealle]<>“-”), [1dallealle])

2

What is the type of this field Registro presenze[Entrata]? I assume it is Time format and set to HH:MM:SS format? If so then you should be able to just use the text() function with a format string like this:

text(Registro presenze[Entrata],"HH:MM")

Take a look at the following article in the help documentation.

|

appsheet_rebrand_logo.png
appsheet_rebrand_logo.png192×192 2.5 KB

| TEXT() |
| - | - |
| | Format as text |

3

First off, thanks for replying. I read the article multiple times before posting and I can’t seem to figure out why it’s not working for me.

Your assumption is correct, it is a time field in the “HH:MM:SS” format and I have checked the “ignore seconds” checkbox in the column definition. What you suggested returns the following error in the parser:

TEXT function with two arguments requires a temporal type and text representing a date format

4

what is the result if you use the following expression?

text(Registro presenze[Entrata])
5

Doing what you just suggested seems to interfere with the rest of the expression, it returns the following error:

Unable to find column ‘Data’, did you mean ‘Tutor’?

Just to make sure I am getting this right, you are suggesting to use this string in my expression, not anywhere else (e. g. in the original field column definition)? if I am getting this right, your syntax suggests you want me to put the string in my original formula thus wrapping TEXT () in SELECT().

6

Yes put this in the expression wherever you need to convert a time field, so for example, you have the following:

TEXT(
  CONCATENATE(
    ANY(
      SELECT(Registro presenze[Entrata],
      AND(
        DAY([Data])="1",
        YEAR([Data])=YEAR(TODAY()-5),
        ([Tirocinante] = [_THISROW].[Tirocinante]),
        MONTH([Data])=MONTH(TODAY() - 5
      )
    ),
    FALSE
   )

I am suggesting this bit would be written as follows:

TEXT(
  CONCATENATE(
    ANY(
      SELECT(TEXT(Registro presenze[Entrata], "HH:MM"),
      AND(
        DAY([Data])="1",
        YEAR([Data])=YEAR(TODAY()-5),
        ([Tirocinante] = [_THISROW].[Tirocinante]),
        MONTH([Data])=MONTH(TODAY() - 5
      )
    ),
    FALSE
   )
7

Ok, thanks for the clarification, although that’s exactly the way I did it (both with and without the “HH:MM”) and it gave me the two errors that I pasted in my replies to your messages.

It’s driving me nuts.

8

OK, your expression is really far too big to debug like this. I can assure you that a time field when wrapped with text([timefield], “HH:MM”) will absolutely display what you want. The problem must be that some of the fields you are trying to convert are either not time fields or you have some syntax error.

Try breaking the expression down into smaller components to test each expression, if necessary you can do this by creating multiple virtual columns to test expressions like

text(Registro presenze[Entrata],"HH:MM")

I also built a spreadsheet which you can use to test expressions without even going through the expression builder, you can find that and instructions on how to use it here:

Spreadsheet to allow for AppSheet Expression testi… - Google Cloud Community

9

what’s puzzling is I have a similar but slightly less complex expression in another column that has the TEXT() function working fine in achieving the goal of removing seconds, here it is

IF(
OR(ISBLANK([2dallealle]), [2dallealle]=“-”),
CONCATENATE(
TEXT(
ANY(
SELECT(
Registro presenze[Entrata],
AND(
DAY([Data])=“2”,
YEAR([Data])=YEAR(TODAY()-5),
([Tirocinante] = [_THISROW].[Tirocinante]),
MONTH([Data])=MONTH(TODAY() - 5)),
FALSE
)
)
),
“-”
,
TEXT(
ANY(
SELECT(
Registro presenze[Uscita],
AND(
DAY([Data])=“2”,
YEAR([Data])=YEAR(TODAY()-5),
([Tirocinante] = [_THISROW].[Tirocinante]),
MONTH([Data])=MONTH(TODAY() - 5)),
FALSE
)
)
)
),
[2dallealle])

10

thank you for taking the time. Will try your spreadsheet!

11

OK I think the issue is the select returns a list even though there is only one item in it, try the following:

TEXT(
  ANY(
    SELECT(
      Registro presenze[Data],
      AND(
        DAY([Data])="1",
        YEAR([Data])=YEAR(TODAY()-5),
        ([Tirocinante] = [_THISROW].[Tirocinante]),
        MONTH([Data])=MONTH(TODAY() - 5)
      ),
      FALSE
    )
  ),
  "HH:MM"
)
12

I wouldn’t consider that an issue ( I have used TEXT() with SELECT() in other expressions and it works just fine, I pasted one of those in one of the replies above)

Tried your last suggestion and it returns:

ANY function is used incorrectly

13

OK I have taken the whole expression and formatted it to make it easier to read, I think this might work:

IFS(
	AND(
		ISBLANK([1dallealle]),
		ISBLANK(
			ANY(
				SELECT(
					Registro presenze[Data],
					AND(DAY([Data])="1",YEAR([Data])=YEAR(TODAY()-5),([Tirocinante] = [_THISROW].[Tirocinante]),MONTH([Data])=MONTH(TODAY() - 5)),
					FALSE
				)
			)
		)
	), 
	"------------",

	AND(
		OR(
			ISBLANK([1dallealle]),
			[1dallealle]="-"
		),
		ISNOTBLANK(
			ANY(
				SELECT(
					Registro presenze[Timestamp],
					AND(DAY([Data])="1",YEAR([Data])=YEAR(TODAY()-5),([Tirocinante] = [_THISROW].[Tirocinante]),MONTH([Data])=MONTH(TODAY() - 5),[Assenza]="Assente"),
					FALSE
				)
			)
		)
	),
	"Assente",

	OR(
		ISBLANK([1dallealle]), 
		[1dallealle] ="-"
	), 
	TEXT(
		CONCATENATE(
			TEXT(
				ANY(
					SELECT(
						Registro presenze[Entrata],
						AND(DAY([Data])="1",YEAR([Data])=YEAR(TODAY()-5),([Tirocinante] = [_THISROW].[Tirocinante]),MONTH([Data])=MONTH(TODAY() - 5)),
						FALSE
					)
				),
				"HH:MM"
			),
			"-",
			ANY(
				SELECT(
					Registro presenze[Uscita],
					AND(DAY([Data])="1",YEAR([Data])=YEAR(TODAY()-5),([Tirocinante] = [_THISROW].[Tirocinante]),MONTH([Data])=MONTH(TODAY() - 5)),
					FALSE
				)
			)
		)
	),

	AND(
		ISNOTBLANK([1dallealle]), 
		[1dallealle]<>"-"
	), 
	[1dallealle]
)
2 Likes
14

thank you very much for taking the time to do all of this. I have tested it and unfortunately it does not remove seconds

EDIT: I honestly don’t know what changed between the first time I tried and now, but it’s working now! thanks a lot!

1 Like