How to sum hours, minutes, or seconds in Outlook

Status
Not open for further replies.
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 :)
 
Status
Not open for further replies.
Thread starter Similar threads 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 New Slipstick.com Articles 0
Diane Poremsky How to Always Process Mail After Business Hours New Slipstick.com Articles 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 Calendar Printing Assistant 6
B Recurring meeting times off by several hours Exchange Server Administration 5
L Delay / Send outgoing mail during certain hours Using Outlook 1
A Emails are Delayed by several hours before appearing in my inbox Using Outlook 4
D Outlook 2007 To Do List: How To Fix Alert That Shows "Due in X Hours" Using Outlook 3
V Outlook 2003 stops receiving email after a few hours. Using Outlook 4
J Appointment times automatically moved 2 hours when I changed time zones...how do I stop this? Using Outlook 1
L Since Exchange 2010 SP1 upgrade, Mail Submission service is restarting every 24 hours in the early m Exchange Server Administration 11
B Outlook 2002 (SP3): This morning two replies I tried to send just sat in my Outbox for several hours. Using Outlook 2
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
E Why does a Mailboximportrequest queue for at least 10 minutes before starting the import? Exchange Server Administration 1
P After window 7 idle for few minutes then outlook 2010 stop send and receive Using Outlook 2
R Outlook takes 3 minutes to load. Using Outlook 2
T New-MailboxExportRequest queues for over 15 minutes before executing. Also seems limited to 1 mailb Exchange Server Administration 2
J Outlook 2007 Reminders show up 6 days in advance when set for 30 minutes. Using W7. Using Outlook 2
T Appointment times shift by minutes. (Outlook 2003) Using Outlook 2
L Outlook Calendar reminder changes to 15 minutes Using Outlook 3
R Reminder does not have "snooze until 5 minutes before start" option anymore Using Outlook 19
J Outlook 2007 conducts send/receive every 35 seconds, even though it is set for 30 minutes Using Outlook 5
R Can't change calendar reminder default from 15 minutes on Outlook 2010 Using Outlook 14
M Problem with meeting requests (default reminder set to 15 minutes by Exchange) Exchange Server Administration 4
J Rule to Forward email but delay delivery 10 minutes Outlook VBA and Custom Forms 2
Similar threads









































Top