1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help need for Slab against Time

Discussion in 'Ask an Excel Question' started by JawaharPrem, Dec 6, 2017.

  1. JawaharPrem

    JawaharPrem Member

    Messages:
    101
    Hi,

    Please help on this requirment, iam working on slab that if
    ex - 11/1/2017 8:49:44 AM to 11/1/2017 9:15:44 AM the slab should be updatd as "8.30-9.30" already i tried in lookup its working for "8-9","9-10"...., but i rebulit for "8.30-9.30" iam not getting the correct outpuut, Pleas help on same. file enclosed for reference Coloum "D","Start time" to be refer.


    Regards
    Jawahar Prem

    Attached Files:

  2. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,344
    Maybe…….

    In N2, copied down :

    =LOOKUP(MOD(D2,1)*1440,{0;510;570;630;690;750;810;870;930;990;1050;1110;1170;1230;1290},{"";"8.30-9.30";"9.30-10.30";"10.30-11.30";"11.30-12.30";"12.30-13.30";"13.30-14.30";"14.30-15.30";"15.30-16.30";"16.30-17.30";"17.30-18.30";"18.30-19.30";"19.30-20.30";"20.30-21.30";"21.30-22.30"})

    Regards
    Bosco
  3. JawaharPrem

    JawaharPrem Member

    Messages:
    101
    Hi Sir,

    Thanks for your time its working file, i learnt one thing today..

    Regards
    Jawahar Prem
  4. p45cal

    p45cal Well-Known Member

    Messages:
    924
    …or N2 copied down:
    =TEXT(FLOOR.PRECISE(D2-1/48,1/24)+1/48,"h.mm")& "-" & TEXT(FLOOR.PRECISE(D2-1/48,1/24)+3/48,"h.mm")
  5. JawaharPrem

    JawaharPrem Member

    Messages:
    101
    Hi Sir,
    thanks to provide new formula, please help to under the formula it may usefull to in other reports..


    Thanks
    Jawahar Prem
  6. p45cal

    p45cal Well-Known Member

    Messages:
    924
    FLOOR rounds down to the nearest multiple. In your case, your slabs are 1 hour long, so that multiple needs to be 1 hour, or 1/24 days (Excel dates are in whole days, and time is a fraction of 1 (6am = 0.25).
    To handle your wanting it to be 9:30-10:30, 10:30;11:30 instead of 9:00-10:00 and 10:00-11:00 etc. we need to take half an hour off before we round down, then add it back again after rounding. Half an hour is 1/48 days.
    FLOOR.PRECISE(D2-1/48,1/24)+1/48
    This gives a decimal number for the start of the 'slab' which you want to see as time; that's what the TEXT function does.
    For the second part (the end of the 'slab'), it's just the same but we want it to be an hour later, so instead of adding back just half an hour, we add back 1 and a half hours, or 3/48 days.
    (FLOOR.PRECISE(D2-1/48,1/24)+3/48
    The rest is just formatting.
    Last edited: Dec 7, 2017 at 11:39 AM
  7. JawaharPrem

    JawaharPrem Member

    Messages:
    101
    Hi Sir,

    Thanks for your time.

Share This Page