+ Reply to Thread
Results 1 to 10 of 10

deduct time from time dd.mm.yyyy hh:mm:ss - dd.mm.yyyy hh:mm:ss

  1. #1
    Registered User
    Join Date
    01-06-2021
    Location
    Hungary
    MS-Off Ver
    2016
    Posts
    6

    deduct time from time dd.mm.yyyy hh:mm:ss - dd.mm.yyyy hh:mm:ss

    Hi Everyone,

    Please help me with the correct formula. I want to deduct the times in column F from the times in column G. Since the times are in the same format I simply use =G2-F2. What get returned in column J seem to be only calculating the minute difference of the two times but ignoring the day difference. How could I correct this?


    Many thanks!
    Attached Images Attached Images

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: deduct time from time dd.mm.yyyy hh:mm:ss - dd.mm.yyyy hh:mm:ss

    You need to format the cells in J as dd:hh:mm:ss
    or
    [hh]:mm:ss
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    01-06-2021
    Location
    Hungary
    MS-Off Ver
    2016
    Posts
    6

    Re: deduct time from time dd.mm.yyyy hh:mm:ss - dd.mm.yyyy hh:mm:ss

    oh yepp..thanks I thought this would be something obvious... as usual :D thanks!

  4. #4
    Registered User
    Join Date
    01-06-2021
    Location
    Hungary
    MS-Off Ver
    2016
    Posts
    6

    Re: deduct time from time dd.mm.yyyy hh:mm:ss - dd.mm.yyyy hh:mm:ss

    Do you maybe have an idea for another one?

    If I want to then check if the result is greater than 24 hours and if it is then the formula to return "YES" for me and if not then "NO". I am trying with this forumula but I suck.

    Many thanks!
    Attached Images Attached Images

  5. #5
    Registered User
    Join Date
    01-06-2021
    Location
    Hungary
    MS-Off Ver
    2016
    Posts
    6

    Re: deduct time from time dd.mm.yyyy hh:mm:ss - dd.mm.yyyy hh:mm:ss

    ah ok, shame on me, E2 is not 5 therefore the NO my formula is correct - so it seems it is time to rest :D

  6. #6
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: deduct time from time dd.mm.yyyy hh:mm:ss - dd.mm.yyyy hh:mm:ss

    Good deal. Please mark this thread as Solved if everything is completed. Thanks

  7. #7
    Registered User
    Join Date
    01-06-2021
    Location
    Hungary
    MS-Off Ver
    2016
    Posts
    6

    Re: deduct time from time dd.mm.yyyy hh:mm:ss - dd.mm.yyyy hh:mm:ss

    New day, new question around this formula so not marking this one as completed yet.

    any idea how I could make this formula calculate only certain hours of a day not the full 24 hours?
    these are service ticket durations which I calculate in column J but in some cases - e.g. if the priority code of the ticket is 5, the formula should
    only calculate if it is a weekday and is in between 6am-22pm, the stop calculating the duration and start running again if it is the next weekday 6 am.

    Just to make it more complex, this is for tickets with priority code 5 but for a different priority code the service duration is different,
    (not 6am-22 pm but considers full week 7*24.

    Many thanks in advance!

  8. #8
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: deduct time from time dd.mm.yyyy hh:mm:ss - dd.mm.yyyy hh:mm:ss

    At this point, you should upload a workbook example of how your data would be laid out (an actual workbook, not an image). It makes it easier for us to try things. See yellow banner at top of page for instructions.

  9. #9
    Registered User
    Join Date
    01-06-2021
    Location
    Hungary
    MS-Off Ver
    2016
    Posts
    6

    Re: deduct time from time dd.mm.yyyy hh:mm:ss - dd.mm.yyyy hh:mm:ss

    Hi,

    I have uploaded my workbook.

    I would need the Response Time in column J to be calculated considering first the Priority Code. If the Priority Code is 1 or 2 then the Response Time is easy
    to calculate as it is 7*24 so no need here that the formula considers if the open time was a weekday or weekend day. But if the priority code is 3, 4, or 5 then
    the response time calculation - which should be calculated on the basis of Open Time - should start only at 6 am and run until 10 pm, time outside this timeframe
    should not add to the response time.
    Does any formula exist for this?
    Many thanks!!!
    Attached Files Attached Files

  10. #10
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: deduct time from time dd.mm.yyyy hh:mm:ss - dd.mm.yyyy hh:mm:ss

    Can you fill in some expected results? For example the first one arrives on a Sunday and is resolved on Monday. Weekend hours don't count so it would just be the time on Monday - 6AM (opening time)?

    Also unclear about priority codes. So 1 and 2 would simply be G2-F2, while 3,4 and 5 consider only working hours?
    Last edited by ChemistB; 01-08-2021 at 02:25 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 3
    Last Post: 04-25-2019, 11:05 AM
  2. Replies: 35
    Last Post: 06-21-2017, 09:56 PM
  3. Replies: 3
    Last Post: 07-14-2016, 06:36 PM
  4. Replies: 7
    Last Post: 11-18-2012, 02:28 PM
  5. [SOLVED] Imported data contains strings dd.mm.yyyy how can I convert to date format dd/mm/yyyy inVB
    By Boormo in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 09-05-2012, 05:48 PM
  6. Copy+Paste macro dd/mm/yyyy to dd/mm/yyyy and NOT dd/mm/yyyy to mm/dd/yyyy
    By uimhirADo in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-08-2012, 03:45 AM
  7. opening excel file -> date format problem: DD/MM/YYYY vs MM/DD/YYYY
    By yung in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-18-2005, 09:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1