How to sum hours, minutes, or seconds in Outlook

Outlook version
Outlook 2010 64 bit
Email Account
IMAP
I am trying to add the time from the email received till the email is responded by every working hours (9 business hours). Eg: Email received on Monday 9AM but responded on Thursday 10AM then the time frame is 28 hours (i.e 9 hours x 3days +1 hour in 4th day).
 
Outlook version
Outlook 2010 64 bit
Email Account
IMAP
Hi Diane, Thanks for replying. I am actually trying to use a formula in a new column. I tired with DateDiff but it is showing me the incorrect result. Infact, I am succeeding in excel with a formule which comes to 75 hours for Email received in A2 cell is 03-04-17 09:00:00 AM and replied time in B2 cell is 13-04-17 12:00:00 =(SUM(INT((WEEKDAY(A12-{2,3,4,5,6})+B12-A12)/7))-1)*("17:00"-"8:00")+MOD(B12,1)-MOD(A12,1). But i am failing to do this in outlook. I know there is no SUM and Mod function in outlook so i have expanded the formulae.

IIf[Last Verb Exec Type]<>"None",(Int((Weekday([Received]-2)+[Last Verb Exec Time]-[Received])/7)+Int((Weekday([Received]-3)+[Last Verb Exec Time]-[Received])/7)+Int((Weekday([Received]-4)+[Last Verb Exec Time]-[Received])/7)+Int((Weekday([Received]-5)+[Last Verb Exec Time]-[Received])/7)+Int((Weekday([Received]-6)+[Last Verb Exec Time]-[Received])/7)*9)-(TIME(HOUR([Received]),MINUTE([Received]),SECOND([Received]))-TIME(9,0,0))*24-(TIME(18,0,0)-TIME(HOUR([Last Verb Exec Time]),MINUTE([Last Verb Exec Time]),SECOND([Last Verb Exec Time])))*24

Please help
 
Outlook version
Outlook 2010 64 bit
Email Account
IMAP
In excel the expanded formule works
=(INT((WEEKDAY(A2-2)+B2-A2)/7)*9+INT((WEEKDAY(A2-3)+B2-A2)/7)*9+INT((WEEKDAY(A2-4)+B2-A2)/7)*9+INT((WEEKDAY(A2-5)+B2-A2)/7)*9+INT((WEEKDAY(A2-6)+B2-A2)/7)*9-(TIME(HOUR(A2),MINUTE(A2),SECOND(A2))-TIME(9,0,0))*24-(TIME(18,0,0)-TIME(HOUR(B2),MINUTE(B2),SECOND(B2)))*24)/24
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
there is an error in it somewhere - i'm getting wrong # of functions error.

Is this a situation where you could run a macro daily (or more or less often) to set the field value?
 
Outlook version
Outlook 2010 64 bit
Email Account
IMAP
Hi Diane, Thanks for immediate reply. This is not a situation (or not looking at a situation) where I run a macro daily. This is an additional field which I am expecting to stamp as soon as email has been responded. Like how last verb executed time.
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
ok, just checking... not sure a macro would be any easier (sorter/less complicated) to get weekdays but there is an vba function that could help (it was written by an excel guru).

is that formula copied out of outlook? It's not working here - something is missing or out of place.
 
Outlook version
Outlook 2010 64 bit
Email Account
IMAP
Ah...finally, after I have broken that lengthy formule into 4 function and applied both in excel and outlook for email received 03-04-17 09:00:00 AM and sent 03-04-2017 12:00:00 PM(by creating 3 custom fields - Weekday, Start time, endtime and SLA (weekday - starttime - endtime)/24) the answer in both the excel and outlook is reflecting the correct answer. But in excel when I format the result into [hh]:mm:ss then it is reflecting the correct time. That format part i am unable to do it outlook.

in Excel

1) Result 9 for =(INT((WEEKDAY(A2-2)+B2-A2)/7)*9+INT((WEEKDAY(A2-3)+B2-A2)/7)*9+INT((WEEKDAY(A2-4)+B2-A2)/7)*9+INT((WEEKDAY(A2-5)+B2-A2)/7)*9+INT((WEEKDAY(A2-6)+B2-A2)/7)*9)

2) Result 0 for =(TIME(HOUR(A2),MINUTE(A2),SECOND(A2))-TIME(9,0,0))*24

3) Result 6 for =(TIME(18,0,0)-TIME(HOUR(B2),MINUTE(B2),SECOND(B2)))*24

4) Result 0.125 =(E2-F2-G2)/24

5) Format above result into [hh]:mm:ss and the result is 03:00:ss

In Outlook

1) Result 9 for IIf([Last Verb Exec Type]="None","None",INT((WEEKDAY([Received]-2)+[Last Verb Exec Time]-[Received])/7)*9+INT((WEEKDAY([Received]-3)+[Last Verb Exec Time]-[Received])/7)*9+INT((WEEKDAY([Received]-4)+[Last Verb Exec Time]-[Received])/7)*9+INT((WEEKDAY([Received]-5)+[Last Verb Exec Time]-[Received])/7)*9+INT((WEEKDAY([Received]-6)+[Last Verb Exec Time]-[Received])/7)*9)

2) Result 0 for IIf([Last Verb Exec Type]<>"None",(TimeSerial(Hour([Received]),Minute([Received]),Second([Received]))-TimeSerial(9,0,0))*24)

3) Result 6 for IIf([Last Verb Exec Type]<>"None",(TimeSerial(18,0,0)-TimeSerial(Hour([Last Verb Exec Time]),Minute([Last Verb Exec Time]),Second([Last Verb Exec Time])))*24)

4) Result 0.125 for ([WorkDays]-[Start Time]-[EndTime])/24

5) unable to format :-(
 
Outlook version
Outlook 2010 64 bit
Email Account
IMAP
Hi Diane,

Thanks for reply. I tried with your suggestion but the result is coming as 00:00. so i broke that formulae into two and created a new field and applied date serial number alone and the result is Tue 30-11-99 12:00 AM for all the emails and later when i applied format the result is 00:00 for all the emails.
But when i applied in excel it is coming as #VALUE!.

Please advise. I am on the verge of completion.
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
[WorkDays]-[Start Time]-[EndTime])/24 gets the correct value? Did you try using
format([WorkDays]-[Start Time]-[EndTime])/24, "hh:mm") ?


This works here to get the time difference - including weekends.

format(IIf([Last Verb Exec Type]<>"None",(TimeSerial(Hour([Last Verb Exec Time]),Minute([Last Verb Exec Time]),Second([Last Verb Exec Time]))-TimeSerial(Hour([Received]),Minute([Received]),Second([Received])))),"h:mm:ss")
 
Outlook version
Outlook 2010 64 bit
Email Account
IMAP
Hi Diane,

I have tried with both the formulas already but the time hours is not going beyond 24 hours. For Email Received time = 24-03-17 10:45:00 AM and Email Actioned time = 19-04-17 11:39:00 AM and the result should be = 142:54:00 but with your suggested formula is showing as 10:06:08. Does the below site work?
Microsoft Access tips: Calculating elapsed time
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
Yeah... I was only looking at the last step in your list to see if it worked to get the hours and minutes properly formatted as hours and minutes since you said it didn't work. I forgot about 24 hours.... i generally answer within 24 hours so all of the ones i checked were under 24 hours. :)

As I mentioned in my first reply, you need to do a datediff - that is needed to go over 24 hours.
The MSDN reference to datediff: DateDiff Function (Visual Basic)

This will get you the proper # of minutes- you'll need to take out the non-business hours -
IIf([Last Verb Exec Type]<>"None",DateDiff("n",[Received],[Last Verb Exec Time]),"")

this is working for total hours & min:
format(IIf([Last Verb Exec Type]<>"None",DateDiff("n",[Received],[Last Verb Exec Time]),"")\60,"00" & format(IIf([Last Verb Exec Type]<>"None",DateDiff("n",[Received],[Last Verb Exec Time]),"") Mod 60,"\:00"))
 
Outlook version
Outlook 2010 64 bit
Email Account
IMAP
Aaaahhhhh......hurray....Finally...I have got to work to go beyond 24 hours....Thanks Diane..Thank you so much. :)

The Result for the above is 613:54 but ideally it should be 142:54. I am trying to figure out with my workings..and will let you know the result. thanks again.
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
Surprised how come Mod function worked when there is no function available in the outlook.
MOD is an operator and the supported functions are pretty much identical (if not completely identical) to the VB functions, so anything that works in either VB or VBA in other office apps should work in outlook, if outlook supports the function it works in. (There are a few Excel functions that won't work in Outlook, not sure about Access functions.)
 
Outlook version
Outlook 2010 64 bit
Email Account
IMAP
If that is the case, Can I go ahead and directly put this formulae =(SUM(INT((WEEKDAY(A3-{2,3,4,5,6})+B3-A3)/7))-1)*("17:00"-"8:00")+MOD(B3,1)-MOD(A3,1).
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
You can try it but it may not work... and you will need to change the Cell references to outlook fields... and you will probably need it in the IIF. I'm not sure if outlook supports {2,3,4,5,6}
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
BTW, MOD is an operator and is the equation, not a function.

From MSDN: Mod Operator (Visual Basic)
Syntax is number1 Mod number2
The result is the remainder after number1 is divided by number2. For example, the expression 14 Mod 4 is 2.

You would need to use ([Last Verb Exec Time],1) MOD ([Received],1) - but that isn't returning any values here (using just that as the formula) because we're dividing the value of Last Verb Exec Time],1 (which is nothing) by the value of [Received],1, which is also nothing.
 
Outlook version
Outlook 2010 64 bit
Email Account
IMAP
Yeah..Yeah...i understand Mod is an equation...Thanks Diane..for taking your time and replying. I am working on it and will let you know once i figure it out :)
 

Similar threads


Top