Calculation between two dates with AM/PM

mboroto_89

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.

Time Difference in Minutes.yxmd

Date Time

Accepted answers

DataNath

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

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

Date diff in minutes - with PM and AM.yxmd

AlexEntz

Hello,

here is how I would do it:

Workflow is attached as well

Time Difference in Minutes.yxmd

grazitti_sapna

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

Thanks!

Time Difference in Minutes.yxmd

All comments

DataNath

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

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

Date diff in minutes - with PM and AM.yxmd

AlexEntz

Hello,

here is how I would do it:

Workflow is attached as well

Time Difference in Minutes.yxmd

mboroto_89

Thanks @DataNath @AlexEntz both solutions work!

Thanks for the swift response..much appreciated.

mboroto_89

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

grazitti_sapna

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

Thanks!

Time Difference in Minutes.yxmd

mboroto_89

Thanks @grazitti_sapna this works as well

mboroto_89

@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

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703