Trying to get the time difference from when a sales order was created and when it was shipped. The only function I could get was DateDiff. Is it possible to get the time in the format of “HOUR:MINUTE”?
=DateDiff( 'MINUTE',[SOShipment.ConfirmedDateTime],[SOOrder.CreatedDateTime])*-1
Best answer by BenjaminCrisman
=Concat(Cstr(DateDiff('h', [SOOrder.OrderDate], [ARInvoice.CreatedDateTime])+':'+Cstr(DateDiff('n', [SOOrder.OrderDate], [ARInvoice.CreatedDateTime]))
Or did I misunderstand and you just want to SUM them into minutes or hours?
Was thinking of using MOD to achieve what I want as follows
Use this to get hours =DateDiff( 'HOUR', [SOOrder.OrderDate],[ARInvoice.CreatedDateTime],)
And this one to get the minutes.
DateDiff( 'MINUTE', [SOOrder.OrderDate],[ARInvoice.CreatedDateTime],)%60)
The issue is now on how to combine the two. Will appreciate any suggestions on how to achieve this.
+19Hi
Can you please share the GI, so that I can build a SQL view and then create GI accordingly with the HH:MM:SS
+4=Concat(Cstr(DateDiff('h', [SOOrder.OrderDate], [ARInvoice.CreatedDateTime])+':'+Cstr(DateDiff('n', [SOOrder.OrderDate], [ARInvoice.CreatedDateTime]))
Or did I misunderstand and you just want to SUM them into minutes or hours?
Was thinking of using MOD to achieve what I want as follows
Use this to get hours =DateDiff( 'HOUR', [SOOrder.OrderDate],[ARInvoice.CreatedDateTime],)
And this one to get the minutes.
DateDiff( 'MINUTE', [SOOrder.OrderDate],[ARInvoice.CreatedDateTime],)%60)
The issue is now on how to combine the two. Will appreciate any suggestions on how to achieve this.
Hello,
I’ve needed a solution to a similar problem; my solution uses the MODULO function as well and is formatted as ‘dd:hh:mm:ss’.
=CONCAT( IIF( DATEDIFF( 's', [SOOrder.UsrOrderDeliveredTime], [ARInvoice.CreatedDateTime]) < 0, '-', '') , CSTR(ABS(DATEDIFF( 's', [SOOrder.UsrOrderDeliveredTime], [ARInvoice.CreatedDateTime]))/86400) ,':' , PADLEFT(CSTR(ABS(DATEDIFF( 's', [SOOrder.UsrOrderDeliveredTime], [ARInvoice.CreatedDateTime]))/3600%24),2,'0'),':' , PADLEFT(CSTR(ABS(DATEDIFF( 's', [SOOrder.UsrOrderDeliveredTime], [ARInvoice.CreatedDateTime]))/60%60),2,'0') ,':' , PADLEFT(CSTR(ABS(DATEDIFF( 's', [SOOrder.UsrOrderDeliveredTime], [ARInvoice.CreatedDateTime]))%60),2,'0'))
I’ve modified my solution in a way that uses your fields as well as your intended formatting:
=CONCAT( IIF( DATEDIFF( 'n', [SOShipment.ConfirmedDateTime], [SOOrder.CreatedDateTime]) < 0, '-', ''), CSTR(ABS(DATEDIFF( 'n', [SOShipment.ConfirmedDateTime], [SOOrder.CreatedDateTime]))/60%60),':', PADLEFT(CSTR(ABS(DATEDIFF( 'n', [SOShipment.ConfirmedDateTime], [SOOrder.CreatedDateTime]))%60),2,'0'))
This should work for what you need, but I may have made a syntax error due to me not spending much time testing this version of the solution.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
