Time and Date - with a twist or not

    I have two columns A and B with times and dates in one cell ie


    Date/Time Received Date/Time Returned
    15/02/2006 12:44 15/02/2006 17:20
    15/02/2006 12:44 15/02/2006 17:06
    15/02/2006 12:44 15/02/2006 17:03
    02/02/2006 14:27 15/02/2006 16:52
    02/02/2006 14:27 15/02/2006 16:44
    15/02/2006 16:32 15/02/2006 16:35
    15/02/2006 11:01 15/02/2006 16:29
    11/01/2006 13:00 15/02/2006 15:24
    02/02/2006 14:27 15/02/2006 15:10


    Now the cell format is ‘custom’ in name convention (UK format)
    dd/mm/yyy hh:mm


    Now in Column 3 I need the difference in Days / Hours / Minutes


    Also ---- I need non-counting days to be avoided – ie Sat and Sun


    There will be series of UpDates to this question with a few twists to the formula


    I don’t mind if a VBA UDF is used or an Excel Function, as a lot of additional will be required


    Many thanks for trying to help me guys – just something nasty I will have to chip away at, later this will all be MS Access


    Jack

    Re: Time and Date - with a twist or not


    Hey, CM Jack,


    Have a look at the attached. Column C shows total real time (column B date/time - column A date/time). Column D subtracts out weekend days. Is this what you wanted?


    Column D formula is:
    =IF(AND(INT(B2)<>INT(A2),WEEKDAY(B2,2)<WEEKDAY(A2,2)),B2-A2-2*(ROUNDUP((B2-A2)/7,0)),B2-A2)


    And columns C and D are formatted CUSTOM "d h:mm"


    EDIT: I tested it and then forgot to add the required ",2" argument to the weekday functions in my first posting. My EDIT corrected the formula displayed and the attachment. Sorry, I also switched to US date format on you, Mate.

    Re: Time and Date - with a twist or not


    Tom
    Your example mixes US and UK formats - i see Your thinking now i think i can get around that part - please keep to UK format (Or US Format)


    Now OK bad news how do i count this if 9am to 5pm times = 1 day ie
    9 am to 5pm = 1 day


    What i need is outside 9-5pm does not count... so counts only inside


    jiuk

    Re: Time and Date - with a twist or not


    Tom
    Sorry mate just read Your post (edit) re the US format, no problems, cought me at first buddy LOL!


    Heck the US and UK are best friends but one thing we will never agree on is the date format - think we all agree on that one?


    Your friend jiuk

    Re: Time and Date - with a twist or not


    Ok, this is getting closer, I think. The main problem i see right away is that if the fractional day is more than eight hours it displays days + 8 hours rather than rounding up the days and giving 0 hours.
    =IF(AND(INT(B2)<>INT(A2),(WEEKDAY(A2,2)+INT(B2-A2))>5),INT(B2-A2-2*(ROUNDUP((B2-A2)/7,0)))+MIN(1/3,MOD(B2-A2,1)),INT(B2-A2)+MIN(1/3,MOD(B2-A2,1)))


    EDIT: Sorry, messed with the date/time formatting and didn't switch it back. (It's now in that funny British syntax!)


    Gotta break for a bit. Will check back in later.

    Re: Time and Date - with a twist or not


    Ok, I think the attached includes all the elements, but they are not nested into a single megaformula.


    My previous attempt had a second problem (that prevents gm's suggestion from working): it gave credit for hours outside 9-5 on the first and last days.


    Have a look at columns F:K in the attached. K finally gets to the answer.
    Column B has the Start Day/Time and Column C the end date/time. (Columns A & D give the actual starting and ending days of the week for reference). Column D the total elapsed time, again for reference.


    Column F the credited start time (not before 9:00 AM) and moved to Monday if a weekend day, and column G the lastest creditable end time (<=5:00 PM) and moved backwards to Friday if a weekend. Respectively:
    =INT(B2)+MIN(17/24,MAX(9/24,MOD(B2,1)))+IF(WEEKDAY(B2,2)>5,8-WEEKDAY(B2,2),0)


    =INT(C2)+MIN(17/24,MAX(9/24,MOD(C2,1)))-MAX(0,WEEKDAY(C2,2)-5)


    Columns H and I the creditable time for the first and last day (Note: this still needs to be corrected if the first or last day is on a weekend -- probably need to use the Weekday function to move the start and end days in columns F and G to a Monday or Friday, respectively, if WEEKDAY(date,2) is 6 or 7):
    Columns H & I:
    =MAX(0,17/24-MOD(F2,1))


    =MOD(G2,1)-9/24


    Column J calcuaties the creditable in-between days (and should work ok given the weekend fixes made to columns F and G.
    =IF(AND(INT(G2)<>INT(F2),(WEEKDAY(F2,2)+INT(G2-F2))>5),INT(G2-F2-2*(ROUNDUP((G2-F2)/7,0))),INT(G2-F2))


    Finally n column K we get:
    =INT(SUM(H2:J2))+INT(MOD(SUM(H2:J2),1/3)*3)+MOD(SUM(H2:J2),1/3)


    Hope this makes soem sense and at least gets you started. I'm not 100% sure I haven't gotten myself completely confused in double think by now. :roll:

    Re: Time and Date - with a twist or not


    Nuts ... more "Fixes" to the previous. Columns F and G formulas are wrong. I moved the start and end dates off of weekends, but failed to adjust the time of days. Start times moved to Monday from a Sat or Sun need to be 9:00 Am on Monday, and end times moved to Friday from a weekend need to be 5:00 PM on Friday. I think these fix this issue.
    =IF(WEEKDAY(B2,2)<6,INT(B2)+MIN(17/24,MAX(9/24,MOD(B2,1))),INT(B2)+8-WEEKDAY(B2,2)+9/24)


    =IF(WEEKDAY(C2,2)<6,INT(C2)+MIN(17/24,MAX(9/24,MOD(C2,1))),INT(C2)-MAX(0,WEEKDAY(C2,2)-5)+17/24)


    Also, the final Column K formula was not adding correctly. It's now this:
    =INT(SUM(H2:J2))+INT(SUM(H2:I2)*3)+MOD(SUM(H2:J2),1/3)


    I hope it's right now. I quitfor the evening. Hope you are asleeep by now!

    Re: Time and Date - with a twist or not


    I must say...that is intense. Nice work. I actually thought of that before about not accounting for the time correctly on the first or last days when i left my house a litle while ago.


    Outstanding though i must say.

    Re: Time and Date - with a twist or not


    Nice Link Krish, very useful - thats one for the bookmarks.

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

    Re: Time and Date - with a twist or not


    Tom / All


    many thanks for Your kindness and help, all be it my slightly slow response, however i was using this base Yesterday and got me out of trouble and also in hot water, ah well what did i expect?


    Many thanks for this well done Tom, I am Ok for now but sure the feed will be lifted as i add yet more twists and might be in need of help - dates and times drive me nuts too.


    Cheers All / Tom


    Jack

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login