# Alteryx Designer Discussions

Andy Uttley, Alteryx ACE, makes music with Alteryx | Math + Music
SOLVED

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

Highlighted
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)

Any idea what why this is returning -2 days?

Highlighted
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

Highlighted
Alteryx Certified Partner

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

Highlighted
Alteryx Certified Partner

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.

Highlighted
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...

Highlighted
Alteryx Certified Partner

You aren't missing anything!

Highlighted
11 - Bolide

@BenMoss wrote:

You aren't missing anything!

First (Date)time for everything...

Highlighted
8 - Asteroid

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

Highlighted
Alteryx Certified Partner
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.
Highlighted
Alteryx Certified Partner

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