Help need for Slab against Time

[JawaharPrem]

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

 

[bosco_yip]

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

 

[JawaharPrem]

Hi Sir,

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

Regards
Jawahar Prem

 

[p45cal]

…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")

 

[JawaharPrem]

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


Thanks
Jawahar Prem

 

[p45cal]

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.

 

[JawaharPrem]

Hi Sir,

Thanks for your time.