How to sum hours, minutes, or seconds in Outlook

Discussion in 'Using Outlook' started by Venkata Murugan Guna, Apr 6, 2017.

  1. Venkata Murugan Guna

    Venkata Murugan Guna

    New 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 :-(

    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.
     
  2. Diane Poremsky

    Diane Poremsky

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

    Venkata Murugan Guna

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

    Diane Poremsky

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

    Venkata Murugan Guna

    New Member
    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
     
  6. Diane Poremsky

    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.
     
  7. Venkata Murugan Guna

    Venkata Murugan Guna

    New Member
    Yeah you are right. I will have to work on this this week end with out any distraction
     
  8. Venkata Murugan Guna

    Venkata Murugan Guna

    New 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.
     
  9. Diane Poremsky

    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.
     
  10. Venkata Murugan Guna

    Venkata Murugan Guna

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

    Diane Poremsky

    Senior Member
    its just the way they handle the sent time.
     
  12. Venkata Murugan Guna

    Venkata Murugan Guna

    New Member
  13. Diane Poremsky

    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)
     
  14. Venkata Murugan Guna

    Venkata Murugan Guna

    New 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?
     
  15. Diane Poremsky

    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.
     
  16. Diane Poremsky

    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.
     
  17. Venkata Murugan Guna

    Venkata Murugan Guna

    New 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
     
  18. Diane Poremsky

    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)
     
  19. Diane Poremsky

    Diane Poremsky

    Senior Member
    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.
     
  20. Diane Poremsky

    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 (Text):
    Copy Source
    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.
     
Loading...

Share This Page