Date and time fucntions

[Anonymous]

Goodmorning experts,

I have two questions regarding a date and a time function.

• Date:

I have a visualisation which shows the number of entered orders.

In the title of this visualisation, I want to show the name of the day and the date for yesterday.

I'm using the following expression:

='Orders entered '

&

if(num(WeekDay(Today()))= 6,(WeekDay(today())-2),(WeekDay(today())-1))

&' '&

if(num(WeekDay(Today()))= 6,(date(today())-2),(date(today())-1))

The expression shows me the numeric WeekDay and the numeric date of yesterday, where this should be the name of the day and the date in 'DD-MM-YYYY'.

Funny thing is, that this expression works as I want for today(), but not for (today()-1).

Any idea how to solve this problem?

• Time:

To improve the data in this chart, I want to show the data of yesterday if the time of opening the sheet is before 16:00:00 ('h:mm:ss').

Otherwise, the chart should show the data of today. So, I'm searching for a proper expression for the following if statement:

if( "date of opening" < 16:00:00, date of yesterday, date of today)

Any idea how to write such an expression?

Hope you guys could help me out!

Cheers,

Mike

[ali_hijazi]

may you please share a sample file so that we can help rapidly

[marcus_sommer]

You don't do today()-1 else weekday(today())-1 and therefore you get a numeric result. By weekday(today()-1) you should get the string-representation of the result. Beside this you could just format a date like you need it - ry it with something like:

= date(today()-(-alt(weekday(today())=6,0)+1), 'WWWW DD.MM.YYYY')

- Marcus

[Anonymous]

Hi Ali,

I've uploaded an example qvf file to my first message in this discussion!

[Anonymous]

Hi Marcus,

your first sentence already solved my first issue, thanks a lot!

Do you also know how to fix this with a time component and a now() expression?

[marcus_sommer]

What do you mean with "... time component and a now() expression"?

[Anonymous]

I want to show the date of yesterday if the time of opening was before 16:00:00.

If the time of opening was after 16:00:00, I want to show the date of today.

FYI, I've uploaded a sample qvf file to my first message in this discussion.

[marcus_sommer · Accepted solution]

Maybe with something like: date(today()-if(hour(now(2))<16,1,0), 'Format')

- Marcus

[Anonymous]

Thanks Marcus, you helped me a lot!:)

[ali_hijazi]

='Orders entered '&

if(num(WeekDay(Today()))= 6,(WeekDay(date(num(today())-2))),(WeekDay(date(num(today()))-1)))

&' '&

if(num(WeekDay(Today()))= 6,(date(num(today())-2)),(date(num(today())-1)))

this is how it should be
be aware that date() expects a number while today() is a timestamp
weekday expects a date