My Date formula is subtracting 2 days instad of 1 day

[collinjd]

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?

[andyuttley · Accepted solution]

Hi @collinjd 

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 

[BenMoss]

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 

[BenMoss]

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.

[andyuttley · Accepted solution]

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

[BenMoss]

You aren't missing anything!

[andyuttley]

---

@BenMoss wrote:

You aren't missing anything!

---

First (Date)time for everything...

[collinjd]

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

[MarqueeCrew]

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

[BenMoss]

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