ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now
alteryx Community

# Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

## My Date formula is subtracting 2 days instad of 1 day

8 - Asteroid

Hi,

I have a formula below to return the date and time as today minus 1 day. However, it is returning the date minus 2 days.

Formula below run today (26th June 2019 is returning the date as 24th June 2019)

DateTimeFormat(DateTimeToUTC(DateTimeAdd (DateTimeToday(), -1,"days")),"%m/%d/%Y")

Any idea what why this is returning -2 days?

11 - Bolide

That will be because you don't have a time associated with your original date (it's a date, rather than a datetime - so it's probably assuming it has JUST turned 26 June). If you include the time it will work correctly (see attached workflow)

Hope that helps

Thanks

Andy

17 - Castor

hmm;

This is an interesting obersrvation, just by breaking down your formula the issue is definitely with the datetimetoUTC() function.

At present it's returning "2019-06-25 23:00:00"

Which, according to my googling skills, is definitely not correct.

Thus when you do datetimetoday(),-1,'days') then it's returning 2019-06-25, then converting that to UTC is doing another day.

Let's tag the master himself @MarqueeCrew

17 - Castor

ARRR, yes; just follow @andyuttley 's guidance.

It appears a datetimetoday() returns todays date at 00:00:00, then the conversion happens with that timestamp.

11 - Bolide

sure I'm missing something, but is it not the use of DateTimeToday where it should be DateTimeNOW()?

e.g.:

In the UK, UTC is -1hr. So Alteryx's logic would be:

today = 26 June, 00:00:00 (time today I think resolves to midnight as opposed to NOW)

-1 day = 25 June, 00:00:00

-1 hr = 24th...

17 - Castor

You aren't missing anything!

11 - Bolide

@BenMoss wrote:

You aren't missing anything!

First (Date)time for everything...

8 - Asteroid

Great thanks I will change to DateTimeNOW() and yes that works. Thanks for your help!

19 - Altair
Great job. Date functions with time become tricky. A note: I prefer DateTimeStart() to DateTimeNow() because it isn't volatile as job runs.

Cheers,

Mark
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Please Subscribe to my youTube channel.
17 - Castor

Great to hear this bought you to a resolution; can you please make sure you mark @andyuttley 's post as a solution (providing it has indeed bought you to one); this way, should people have a similar issue and search the community, this result will be prioritised 😄

Ben

Labels