Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Goodmorning experts,
I have two questions regarding a date and a time function.
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?
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
Maybe with something like: date(today()-if(hour(now(2))<16,1,0), 'Format')
- Marcus
may you please share a sample file so that we can help rapidly
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
Hi Ali,
I've uploaded an example qvf file to my first message in this discussion!
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?
What do you mean with "... time component and a now() expression"?
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.
Maybe with something like: date(today()-if(hour(now(2))<16,1,0), 'Format')
- Marcus
Thanks Marcus, you helped me a lot!:)
='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