How to sum hours, minutes, or seconds in Outlook

Status
Not open for further replies.
Hi Diane,

I have applied alternative to Mod function and it worked great. but i am hitting the wall at beyond 24 hour format :-(

Working Day field - correct answer both in excel and outlook : 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]-2)+[Last Verb Exec Time]-[Received])/6))*(TimeSerial(18,0,0)-TimeSerial(9,0,0))

Mod for end time Field - correct answer both in excel and outlook : IIf([Last Verb Exec Type]<>"None",CDbl([Last Verb Exec Time])-(Int(CDbl([Last Verb Exec Time])/1)*1))

Mod for received time field - correct answer both in excel and outlook :
CDbl([Received])-(Int(CDbl([Received])/1)*1)

Final Field = Working day field + End time file - Start time field = 6.329074. This is also corrrect in excel and outlook.

But failed to get hours beyond 24 hours. :-(. Please help. I scratched my head for almost 3 days.
 
I think you need to use datediff to get the # of days/hours between two dates.
 
You need to get the total elapsed time then subtract the non-working time.
 
This is going beyond my skills o_O. Let me try mean while if you could also lend me your hand that would be of great help
 
it's beyond my skills too. :) Actually, the bigger problem is thinking in time. The logic of it is to get the time difference, then get the # of hours to subtract for non-working hours then convert to hours and minutes.
 
Hi Diane, Hope you are good. I am still working on this (at my leisure time) and found the difference between Last verb executed time and Modified time. While creating new field for Received time, Modified time and Last verb executed time to capture seconds (hh:mm:ss). i see there is a difference in Modified time vs Last verb executed time. Moreover, Last verb executed time is not giving seconds. what is the difference between modified and LVET?. Please help.
 
Last verb is when you reply or forward; modified is doing any thing that changes the message - read, reply, forward, categorize, flag, move... etc.
 
Oh ok. But why is that last verb not capturing seconds? For all the emails it is showing as "00" in seconds
 
its just the way they handle the sent time.
 
i didn't test it, but the commenters on that thread say it does what they need... so for your situation, you need to reverse engineer it, because you want to know the # of working hours between the mail arriving and the reply, right?

in their example of the function this sets the due date based on 36 working hours. You could use this to calculate how much over or under the target you were...
LDate = GetTargetDate(myMail.ReceivedTime, 36)
 
Yes Diane, you are right I want to know the # of working hours between the mail arriving and the reply. So you are saying if I add LDate = GetTargetDate(myMail.ReceivedTime, 36) in outlook new field will this populate the desired result?
 
No, that will get you a due date 36 working hours in the future - basically, the formula is saying
date must finish in the future = GetTargetDate(myMail.ReceivedTime, 36 working hours)

what you need to do is get the working hours between the received and completed dates. The formula would read like this:
number of working hours = GetWorkingHours(myMail.ReceivedTime, date of action)


You can compare the due date with the actual reply date and if they are close, can subtract from the 36 hours (or whatever value is close to your usual response time) to get the exact time, but there are limitations, like if the actual date is a different day - you'll still need to remove the non-working hours in between.
 
i wonder... if you could work this function into it.
Create Outlook appointments for every nn workday

it skips weekends and holidays - so you'll get full days between two dates. You just need to calculate the hours (and min) into the received & replied workdays then calculate the hours to add or subtract from the final date.

it's also possible Chip has an excel function that gets the working hours difference at
Default Excel Redirect - and excel functions are fairly easy to work into outlook.
 
I am so happy that you are responding to my questions. I wil explore on this but I am actually looking a solution inside outlook as against any other applications interference
 
bingo... A Better NETWORKDAYS

You'll pass the start date (received) and end date (replied) to get a whole # of days. Then you need to calculate the # of hours between the time of day it was received and replied. If received is 9AM and replied is 3 PM, you'll add 6 hours, if received at 4 pm and replied at 9 am, you add an hour. (assuming 9 - 5 work day)
 
I am actually looking a solution inside outlook as against any other applications interference
Yeap... Chip's functions work in Outlook. At most, you'll need to tweak it a little. (I have 2 or 3 worked into macros on slipstick).

The networkdays function doesn't need to be tweaked - it doesn't appear to use anything specifically in the Excel object model.
 
This macro uses Chip's better networkdays function - to test, select a message you replied to and run the getdate macro. Look at the results in the Immediate window.

Code:
Sub GetDate()
Dim Item As MailItem
Dim propertyAccessor As Outlook.propertyAccessor
Dim rDate As Date
Set Item = Application.ActiveExplorer.Selection.Item(1)
Set propertyAccessor = Item.propertyAccessor

rDate = propertyAccessor.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x10820040")
Debug.Print Item.ReceivedTime, rDate
Debug.Print NetWorkdays2(Item.ReceivedTime, rDate, 65)
End Sub

and returns this for two messages in my test mailbox -
Received Replied Days between
9/28/2017 9:33:31 PM 9/29/2017 3:45:00 PM 1
8/31/2017 9:22:34 AM 10/13/2017 2:51:00 PM 31

now we know the days - you only need the hours, which shouldn't be hard but just thinking about that logic makes my head hurt. lol

3:45 is 6.75 into the workday... this one is actually easy since the day is one - it took 6.75 hours to respond...

lets assume it was 2 days, we'd multiply # of days x hours in a workday, so 2 x 8 = 16. Then get the difference between the time it was replied and the workday length, 8 - 6.75 = 1.25. Subtract 1.25 from 16 to get the hours needed for the reply.

if you need days & hours, it might be easier to do
(#days - 2) + (8 - mid-day received time) + (8 - mid-day reply time)
full # of days and partial day received + partial day replied

on my 31 day test, it would be (in round numbers)
(31-2) Days (8-.25) + (8-5.75) hours or 29 days, 10 hours.
 
Status
Not open for further replies.
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