0

enter image description here

The code below is working well to calculate the time between two dates and excluding weekends. However, I would like to modify it to just return the number of minutes in number format so I can do some averages on that column.

enter image description here

=IF(OR(ISBLANK([Quote Start Time]),ISBLANK([Quote Complete Time])),"",IF(AND((WEEKDAY([Quote Complete Time],2))<(WEEKDAY([Quote Start Time],2)),((WEEKDAY([Quote Start Time],2))-(WEEKDAY([Quote Complete Time],2)))>1),(((DATEDIF([Quote Start Time],[Quote Complete Time],"D")))-(FLOOR((DATEDIF([Quote Start Time],[Quote Complete Time],"D"))/7,1)*2)-2),(((DATEDIF([Quote Start Time],[Quote Complete Time],"D")))-(FLOOR((DATEDIF([Quote Start Time],[Quote Complete Time],"D"))/7,1)*2)))&" days, "&TEXT(MOD([Quote Complete Time]-[Quote Start Time],1),"hh "" hrs,"" mm "" mins"""))

The second answer is working great to show minutes but it is off by one minute. Any ideas on that? enter image description here

2 Answers 2

1

I changed the *1440 to *1448 and that achieved the desired results!

1
  • Hi Shannon Kearney. I'm glad to hear that your problem has been solved. If the answer is helpful to you, you can accept it as answer. The behavior will help others who meet the similar issue in the future quickly find the correct answer. Commented Dec 30, 2021 at 6:30
0

Please use the below Formula:

=(IF(OR(ISBLANK([Quote Start Time]),ISBLANK([Quote Complete Time])),"",IF(AND((WEEKDAY([Quote Complete Time],2))<(WEEKDAY([Quote Start Time],2)),((WEEKDAY([Quote Start Time],2))-(WEEKDAY([Quote Complete Time],2)))>1),(((DATEDIF([Quote Start Time],[Quote Complete Time],"D")))-(FLOOR((DATEDIF([Quote Start Time],[Quote Complete Time],"D"))/7,1)*2)-2),(((DATEDIF([Quote Start Time],[Quote Complete Time],"D")))-(FLOOR((DATEDIF([Quote Start Time],[Quote Complete Time],"D"))/7,1)*2)))))*1440+INT(MOD([Quote Complete Time]-[Quote Start Time],1)*1440)

enter image description here

enter image description here

1
  • Works like a charm except it appears to be off by a minute. Any ideas about that? Commented Dec 29, 2021 at 14:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.