+ Reply to Thread
Results 1 to 2 of 2

Required difference between two dates/times in hours

  1. #1
    Registered User
    Join Date
    04-24-2013
    Location
    hyderabad
    MS-Off Ver
    Excel 2010
    Posts
    18

    Post Required difference between two dates/times in hours

    Hi,

    I am using the following formula for getting difference between two dates/times in hours.

    =(NETWORKDAYS(A1,B1,)-1)*9+IF(NETWORKDAYS(B1,B1),MEDIAN(MOD(B1,1)*24,17, 8),17)-IF(NETWORKDAYS(A1,A1),MEDIAN(MOD(A1,1)*24,17,8),8).

    Need some following changes in the above below formula

    1. From the above formula if i want to one holiday then how to calculate.
    2. From the above formula result is coming like 0.75 (actual time is 45mins) insted of this can we get the reslut in time fomula like 00:45 or 1:20(1 hr 20mins).

    Please help on this.

    Regards
    Kasi

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Required difference between two dates/times in hours

    Hello Kasi,

    list your holidays on the worksheet, e.g. in H2:H10 and then use this version

    =(NETWORKDAYS(A1,B1,H$2:H$10)-1)*("17:00"-"8:00")+IF(NETWORKDAYS(B1,B1,H$2:H$10),MEDIAN(MOD(B1,1),"17:00","8:00"),"17:00")-MEDIAN(NETWORKDAYS(A1,A1,H$2:H$10)*MOD(A1,1),"17:00","8:00")

    format result cell as [h]:mm with the square brackets used to ensure that hours over 24:00 will display
    Audere est facere

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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