Inspire EMEA 2022 On-Demand is live! Watch now, and be sure to save the date for Inspire 2023 in Las Vegas next May.

Alteryx Designer Discussions

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

Calculation between two dates with AM/PM

mboroto_89
8 - Asteroid

Hi, 

I want to calculate the difference in time(in minutes) between two dates. My dates are as attached

 

i want to see the total difference in minutes between  Completion_Time and Complete_Before.

 

If Completion_Time is after the  Complete_Before then return "Late"

if Completion_Time is before the  Complete_Before then return "Early"

 

The tricky part is with the AM/PM part.

10 REPLIES 10
DataNath
16 - Nebula

The following should do the trick @mboroto_89: - have attached the workflow too:

 

 

 

DateTimeDiff(DateTimeParse([Complete_Before], '%d %b %Y %I:%M:%S %p'),DateTimeParse([Completion_Time], '%d %b %Y %I:%M:%S %p'),'minute')

 

 

 

DataNath_0-1652866807533.png

 

If you just want the raw difference, without negatives, then wrap it in abs(), like so:

 

 

abs(DateTimeDiff(DateTimeParse([Complete_Before], '%d %b %Y %I:%M:%S %p'),DateTimeParse([Completion_Time], '%d %b %Y %I:%M:%S %p'),'minute'))

 

And for the Early/Late flag, you can use:

 

if DateTimeParse([Completion_Time], '%d %b %Y %I:%M:%S %p') > DateTimeParse([Complete_Before], '%d %b %Y %I:%M:%S %p') then 'Late' else 'Early' endif

 

DataNath_1-1652867434519.png

 

 

AlexEntz
8 - Asteroid

Hello,

 

here is how I would do it:

 

AlexEntz_0-1652867491636.png

 

 

 

Workflow is attached as well

 

mboroto_89
8 - Asteroid

Thanks @DataNath @AlexEntz both solutions work!

Thanks for the swift response..much appreciated.

mboroto_89
8 - Asteroid

Thanks @AlexEntz the new dates will also play a role in my workflow, many thanks!

grazitti_sapna
17 - Castor

@mboroto_89, is another way to solve this problem. I hope this helps!

 

grazitti_sapna_0-1652868537739.png

 

Thanks!

Sapna Gupta
mboroto_89
8 - Asteroid

Thanks @grazitti_sapna this works as well

grazitti_sapna
17 - Castor

@mboroto_89 , thanks and glad it worked.

Sapna Gupta
mboroto_89
8 - Asteroid

@DataNath @AlexEntz @grazitti_sapna using the Complete Before Column, are we able to get which day of the week this was? e.g Monday/Saturday?

grazitti_sapna
17 - Castor

@mboroto_89  this formula will help, DateTimeFormat([New Value ],"%A"). Just replace new value with the complete_before field. 

grazitti_sapna_0-1652901818268.png

 

Sapna Gupta
Labels