# How to sum hours, minutes, or seconds in Outlook

#### Venkata Murugan Guna

Member
Hi Diane,

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

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 :

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.

#### Diane Poremsky

Senior Member
I think you need to use datediff to get the # of days/hours between two dates.

#### Venkata Murugan Guna

Member
Hi Diane,
Datediff gives the result including weekends but I am looking at working days

#### Diane Poremsky

Senior Member
You need to get the total elapsed time then subtract the non-working time.

#### Venkata Murugan Guna

Member
This is going beyond my skills . Let me try mean while if you could also lend me your hand that would be of great help

#### Diane Poremsky

Senior Member
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.

#### Venkata Murugan Guna

Member
Yeah you are right. I will have to work on this this week end with out any distraction

#### Venkata Murugan Guna

Member
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.

#### Diane Poremsky

Senior Member
Last verb is when you reply or forward; modified is doing any thing that changes the message - read, reply, forward, categorize, flag, move... etc.

#### Venkata Murugan Guna

Member
Oh ok. But why is that last verb not capturing seconds? For all the emails it is showing as "00" in seconds

#### Diane Poremsky

Senior Member
its just the way they handle the sent time.

#### Diane Poremsky

Senior Member
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)

#### Venkata Murugan Guna

Member
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?

#### Diane Poremsky

Senior Member
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.

#### Diane Poremsky

Senior Member
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.

#### Venkata Murugan Guna

Member
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

#### Diane Poremsky

Senior Member
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)

#### Diane Poremsky

Senior Member
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.

#### Diane Poremsky

Senior Member
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 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.