How to sum hours, minutes, or seconds in Outlook

Status
Not open for further replies.
Venkata Murugan Guna

Venkata Murugan Guna

Member
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).
 
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
 
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
 
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.
 
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.
 
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 :-(
 
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.
 
[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")
 
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
 
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"))
 
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.
 
Venkata Murugan Guna said:
Surprised how come Mod function worked when there is no function available in the outlook.
Click to expand...
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.)
 
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).
 
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}
 
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.
 
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 :-)
 
Status
Not open for further replies.
< Outlook Requesting Phone Number??? | some tasks are disappearing >
Similar threads
Thread starter Title Forum Replies Date
L Outlook 2019 MAC sync error after working for 4 hours Using Outlook 1
G Calendar View in Outlook Office 365 - Doesn't show enough hours, and the 30/60 min choice isn't the solution Using Outlook 4
GregS Outlook mail arrives in batches, sometimes hours late Using Outlook 1
Diane Poremsky How to Process Mail After Business Hours Using Outlook 0
Diane Poremsky How to Always Process Mail After Business Hours Using Outlook 0
C Need rule to alert when an email has not been replied to within 24 hours Using Outlook 1
M Outlook Is Disconnected In the Morning Or After Few Hours Exchange Server Administration 1
L Schedule Assistant shows wrong working hours Using Outlook 1
M Run rules after mail is 24 hours old Using Outlook 1
B Microsoft office outlook 2007 showing configuring outlook accounts for hours Using Outlook 6
S Emails during business hours ONLY. Using Outlook 1
P Change the hours in a working day Using Outlook 6
B Recurring meeting times off by several hours Exchange Server Administration 5
K report of Available hours for a team of staff from OUtlook Calenda Outlook VBA and Custom Forms 2
R Unable to set outlook task due date in hours Outlook VBA and Custom Forms 1
D Desperate hours BCM (Business Contact Manager) 1
A Daily hours of recurring/ multi-day appts, & accessing mult calend Outlook VBA and Custom Forms 2
B iCloud for Windows 11.2 disconnects by itself after 30-45 minutes Using Outlook 9
D How to forward each email x minutes after it arrives in inbox and hasn't been moved or deleted? Using Outlook 1
JessicaMB Every 5 minutes I get an ICS error Using Outlook 1
S Outlook.com set up as EAS in Outlook 2013 – Emails can take up to 25 minutes to arrive Using Outlook.com accounts in Outlook 9
M My Outlook notifications are off by 25 minutes Using Outlook 0
J Rule to Forward email but delay delivery 10 minutes Outlook VBA and Custom Forms 2

Similar threads

Back
Top