# Calculate band on Hours/Time passed



## farhan11941234 (Dec 15, 2022)

Please help me out to figure out a formula to calculate Date/Time Base Bins (See attached Image).Whenever The ID Creation(Column A) on Current Day and Time 3:00 PM to 6:00 PM, In Column C "> 3 to 6 PM" , Current Date and time between 6 to 7 PM in Column C result should be " > 6 to 7 PM", after 7:00 PM , in Column C should be "> 7:00 PM onwards.
when time > 14 Hours it should be >14 Hours and continue the this logic to >24 and >48 hours.
I would be very thankful to help me devolope the formula for this.

Sorry, for not using the XL2BB becuase of the admin Limitations.

Thanks


----------



## farhan11941234 (Dec 15, 2022)

I would be really helpful if the solution formula based on Column B where Hours are becuase in column A Date time is a text  field in UK time formate dd/mm/yyyy
and its difficult for me to change it back to US format which often result errors since start date from 1 to 11 becomes month rather than days.


----------



## jdellasala (Dec 15, 2022)

I hope you're willing to try Power Query. It is set up to handle complicated time calculations like this a LOT better than Excel!
Starting with this Table named *Timesheet*:
Book1AB1Time StampNow212/14/22 12:12 AM12/15/22 3:03 PM312/13/22 3:33 PM12/15/22 3:03 PM412/14/22 7:56 AM12/15/22 3:03 PM512/13/22 6:08 PM12/15/22 3:03 PM612/13/22 6:59 PM12/15/22 3:03 PM712/13/22 9:38 AM12/15/22 3:03 PM812/13/22 1:48 AM12/15/22 3:03 PM912/14/22 3:27 AM12/15/22 3:03 PM1012/13/22 5:23 PM12/15/22 3:03 PM1112/14/22 4:48 AM12/15/22 3:03 PM1212/13/22 5:37 PM12/15/22 3:03 PM1312/13/22 2:25 PM12/15/22 3:03 PM1412/14/22 11:16 PM12/15/22 3:03 PM1512/13/22 12:14 PM12/15/22 3:03 PM1612/14/22 11:20 AM12/15/22 3:03 PM1712/14/22 5:22 AM12/15/22 3:03 PM1812/14/22 1:56 PM12/15/22 3:03 PM1912/13/22 11:26 AM12/15/22 3:03 PM2012/14/22 7:58 AM12/15/22 3:03 PM2112/14/22 10:31 PM12/15/22 3:03 PMSheet1
Pulled the Table into Power Query, 

```
let
    Source = Excel.CurrentWorkbook(){[Name="Timesheet"]}[Content],
    ChangedType = Table.TransformColumnTypes(Source,{{"Time Stamp", type datetime}, {"Now", type datetime}}),
    InsertedTimeSubtraction = Table.AddColumn(ChangedType, "Total Hours", each [Now] - [Time Stamp], type duration),
    CalculatedTotalHours = Table.TransformColumns(InsertedTimeSubtraction,{{"Total Hours", Duration.TotalHours, type number}}),
    RoundedOff = Table.TransformColumns(CalculatedTotalHours,{{"Total Hours", each Number.Round(_, 1), type number}})
in
    RoundedOff
```
and just a few easy steps gets the answer, resulting in this:
Book1DEF1Time StampNowTotal Hours212/14/2022 00:1212/15/2022 15:0338.8312/13/2022 15:3312/15/2022 15:0347.5412/14/2022 07:5612/15/2022 15:0331.1512/13/2022 18:0812/15/2022 15:0344.9612/13/2022 18:5912/15/2022 15:0344.1712/13/2022 09:3812/15/2022 15:0353.4812/13/2022 01:4812/15/2022 15:0361.2912/14/2022 03:2712/15/2022 15:0335.61012/13/2022 17:2312/15/2022 15:0345.71112/14/2022 04:4812/15/2022 15:0334.21212/13/2022 17:3712/15/2022 15:0345.41312/13/2022 14:2512/15/2022 15:0348.61412/14/2022 23:1612/15/2022 15:0315.81512/13/2022 12:1412/15/2022 15:0350.81612/14/2022 11:2012/15/2022 15:0327.71712/14/2022 05:2212/15/2022 15:0333.71812/14/2022 13:5612/15/2022 15:0325.11912/13/2022 11:2612/15/2022 15:0351.62012/14/2022 07:5812/15/2022 15:0331.12112/14/2022 22:3112/15/2022 15:0316.5Sheet1
I obviously didn't use your sample. I generated the Time Stamp column like this:
Book1L1Timestamp212/14/22 2:59 PMSheet1Cell FormulasRangeFormulaL2:L21L2=RANDARRAY(20,,DATEVALUE("12/13/2022"),DATEVALUE("12/14/2022"),TRUE)+RANDARRAY(20,,0,1,FALSE)Dynamic array formulas.
Once I had the Date and Time, I Copy/Paste Valued the column, added NOW() to generate the stop time (not in sample) and let Power Query do the rest.
To do this with Formulas is much more difficult. Give it a try!


----------



## farhan11941234 (Dec 16, 2022)

that was helpful but it did not solve my problem, becuause the Hours calculation was including when I get the Data from database.What I need is to create 
a Column next to hours column Name Hours Bands.
The formula I used for calculation is pasted below , Now I like to Have if the ID Creation Date is current Date and ID Creation time is between 3:00PM to 06:00PM
In Hours Band Column Should be "> 3 to 6PM" and if creation time between 6 to 7 then "6 to 7 PM" and next is when ID creation time > 7 then ">7 PM Onward". This Band for current date only as when the hours exceeds > 14  then " > 14 Hours" , ">24 Hours", "> 48 Hours".
I hope that you understand what I am trying to acheive.  


Hours in Column "B"
=IF(B2>48,">48 hours", IF(B2>18, ">18 hours, <48 hours", "<18 hours"))


----------



## jdellasala (Dec 16, 2022)

Sorry I didn't understand that. What you need is an APROXIMATE Lookup. This may not be exactly what you need, but should show you how to do what you need:
Book2ABCDEF1Time StampHoursCategoryTimeCategory212/14/22 12:12 AM38.8Midnight to 3 AM12:00 AMMidnight to 3 AM312/13/22 3:33 PM47.5Noon to 3 PM3:00 AMMidnight to 3 AM412/14/22 7:56 AM31.13 AM to 6 AM6:00 AM3 AM to 6 AM512/13/22 6:08 PM44.93 PM to 6 PM9:00 AM6 AM to 9 AM612/13/22 6:59 PM44.13 PM to 6 PM12:00 PM9 AM to Noon712/13/22 9:38 AM53.46 AM to 9 AM3:00 PMNoon to 3 PM812/13/22 1:48 AM61.2Midnight to 3 AM6:00 PM3 PM to 6 PM912/14/22 3:27 AM35.6Midnight to 3 AM9:00 PM6 PM to 9 PM1012/13/22 5:23 PM45.7Noon to 3 PM11:59 PM9 PM to Midnight1112/14/22 4:48 AM34.2Midnight to 3 AM1212/13/22 5:37 PM45.4Noon to 3 PM1312/13/22 2:25 PM48.69 AM to Noon1412/14/22 11:16 PM15.86 PM to 9 PM1512/13/22 12:14 PM50.89 AM to Noon1612/14/22 11:20 AM27.76 AM to 9 AM1712/14/22 5:22 AM33.7Midnight to 3 AM1812/14/22 1:56 PM25.19 AM to Noon1912/13/22 11:26 AM51.66 AM to 9 AM2012/14/22 7:58 AM31.13 AM to 6 AM2112/14/22 10:31 PM16.56 PM to 9 PMSheet1Cell FormulasRangeFormulaC2:C21C2=XLOOKUP(TIMEVALUE(TEXT([@[Time Stamp]],"h:mm AM/PM")),TimeCategories[Time],TimeCategories[Category],,-1)
The Category table shows how much of a pain Time can be!
Note that the Lookup Value in the XLOOKUP formula *TIMEVALUE(TEXT([@[Time Stamp]],"h:mm AM/PM"))* is what I needed because the values in the Time Stamp column were actual Date/Time values. You would need to adapt depending on what format your data comes in as. It does need to be in a numeric format though, so that you can do the approximate lookup.
The Approximate Value part of the XLOOKUP formula is the *-1* fourth parameter *[match_mode]* part of the formula. What that means is that XLOOKUP will find the first value in the *lookup_array* that is as large as possible without being more than the *lookup_value* and return the corresponding *return_array* item.

Hope that gets you on the right track!


----------



## Dave Patton (Dec 16, 2022)

Your requirements are not clear.
A concise example with expected results would help. N.B. You can post an extract with the forum's tool named XL2BB.
You can review the following which is a different spin based on post #5.

Time.xlsmABCDEF1Time StampHoursTimeCategory214-Dec-22 0:1238.8Midnight to 3 AM0Midnight to 3 AM313-Dec-22 15:3347.53 PM to 6 PM03:003 AM to 6 AM414-Dec-22 7:5631.16 AM to 9 AM06:006 AM to 9 AM513-Dec-22 18:0844.96 PM to 9 PM09:009 AM to Noon613-Dec-22 18:5944.16 PM to 9 PM12:00Noon to 3 PM713-Dec-22 9:3853.49 AM to Noon15:003 PM to 6 PM813-Dec-22 1:4861.2Midnight to 3 AM18:006 PM to 9 PM914-Dec-22 3:2735.63 AM to 6 AM21:009 PM to Midnight1013-Dec-22 17:2345.73 PM to 6 PM1114-Dec-22 4:4834.23 AM to 6 AM1213-Dec-22 17:3745.43 PM to 6 PM1313-Dec-22 14:2548.6Noon to 3 PM1414-Dec-22 23:1615.89 PM to Midnight1513-Dec-22 12:1450.8Noon to 3 PM1614-Dec-22 11:2027.79 AM to Noon1714-Dec-22 5:2233.73 AM to 6 AM1814-Dec-22 13:5625.1Noon to 3 PM1913-Dec-22 11:2651.69 AM to Noon2014-Dec-22 7:5831.16 AM to 9 AM2114-Dec-22 22:3116.59 PM to Midnight10dCell FormulasRangeFormulaD2:D21D2=XLOOKUP(MOD(A2,1),$E$2:$E$9,$F$2:$F$9,,-1)


----------



## farhan11941234 (Dec 17, 2022)

jdellasala said:


> Sorry I didn't understand that. What you need is an APROXIMATE Lookup. This may not be exactly what you need, but should show you how to do what you need:
> Book2ABCDEF1Time StampHoursCategoryTimeCategory212/14/22 12:12 AM38.8Midnight to 3 AM12:00 AMMidnight to 3 AM312/13/22 3:33 PM47.5Noon to 3 PM3:00 AMMidnight to 3 AM412/14/22 7:56 AM31.13 AM to 6 AM6:00 AM3 AM to 6 AM512/13/22 6:08 PM44.93 PM to 6 PM9:00 AM6 AM to 9 AM612/13/22 6:59 PM44.13 PM to 6 PM12:00 PM9 AM to Noon712/13/22 9:38 AM53.46 AM to 9 AM3:00 PMNoon to 3 PM812/13/22 1:48 AM61.2Midnight to 3 AM6:00 PM3 PM to 6 PM912/14/22 3:27 AM35.6Midnight to 3 AM9:00 PM6 PM to 9 PM1012/13/22 5:23 PM45.7Noon to 3 PM11:59 PM9 PM to Midnight1112/14/22 4:48 AM34.2Midnight to 3 AM1212/13/22 5:37 PM45.4Noon to 3 PM1312/13/22 2:25 PM48.69 AM to Noon1412/14/22 11:16 PM15.86 PM to 9 PM1512/13/22 12:14 PM50.89 AM to Noon1612/14/22 11:20 AM27.76 AM to 9 AM1712/14/22 5:22 AM33.7Midnight to 3 AM1812/14/22 1:56 PM25.19 AM to Noon1912/13/22 11:26 AM51.66 AM to 9 AM2012/14/22 7:58 AM31.13 AM to 6 AM2112/14/22 10:31 PM16.56 PM to 9 PMSheet1Cell FormulasRangeFormulaC2:C21C2=XLOOKUP(TIMEVALUE(TEXT([@[Time Stamp]],"h:mm AM/PM")),TimeCategories[Time],TimeCategories[Category],,-1)
> The Category table shows how much of a pain Time can be!
> Note that the Lookup Value in the XLOOKUP formula *TIMEVALUE(TEXT([@[Time Stamp]],"h:mm AM/PM"))* is what I needed because the values in the Time Stamp column were actual Date/Time values. You would need to adapt depending on what format your data comes in as. It does need to be in a numeric format though, so that you can do the approximate lookup.
> ...


Thanks you so much for your time, With your help I am able to get the desired result. I also used used a macro for date conversion.

Formula  = =IF(AND(DAY(A2)=DAY(TODAY()),B2<14),XLOOKUP(TIMEVALUE(TEXT(TIME(HOUR(A2),MINUTE(A2), SECOND(A2)),"h:mm AM/PM")),$C$2:$C$23,$D$2:$D$23,,-1),IF(B2>72,">72 Hours",IF(B2>48,">48 Hours",IF(B2>36,">36 Hours",IF(B2>24,">24 Hours",IF(B2>14,">14 Hours","<14 Hours"))))))


----------



## Dave Patton (Dec 17, 2022)

Are the brackets that you are using correct?  What should show for 3:27?
You could use a lookup for the hours part.

Time.xlsmHIJKL1HoursText2oror0<= 14 Hours3>36 Hours> 36 Hours> 36 Hours14.001> 14 Hours424.001> 24 Hours536.001> 36 Hours672.001> 72 Hours10dCell FormulasRangeFormulaH3H3=IF(B2>72,">72 Hours",IF(B2>48,">48 Hours",IF(B2>36,">36 Hours",IF(B2>24,">24 Hours",IF(B2>14,">14 Hours","<14 Hours")))))I3I3=LOOKUP(B2,K2:L6)J3J3=LOOKUP(B2,aL)

J3 Names the lookup information.


----------



## jdellasala (Dec 17, 2022)

Dave Patton said:


> Your requirements are not clear.
> A concise example with expected results would help. N.B. You can post an extract with the forum's tool named XL2BB.
> You can review the following which is a different spin based on post #5.
> 
> Time.xlsmABCDEF1Time StampHoursTimeCategory214-Dec-22 0:1238.8Midnight to 3 AM0Midnight to 3 AM313-Dec-22 15:3347.53 PM to 6 PM03:003 AM to 6 AM414-Dec-22 7:5631.16 AM to 9 AM06:006 AM to 9 AM513-Dec-22 18:0844.96 PM to 9 PM09:009 AM to Noon613-Dec-22 18:5944.16 PM to 9 PM12:00Noon to 3 PM713-Dec-22 9:3853.49 AM to Noon15:003 PM to 6 PM813-Dec-22 1:4861.2Midnight to 3 AM18:006 PM to 9 PM914-Dec-22 3:2735.63 AM to 6 AM21:009 PM to Midnight1013-Dec-22 17:2345.73 PM to 6 PM1114-Dec-22 4:4834.23 AM to 6 AM1213-Dec-22 17:3745.43 PM to 6 PM1313-Dec-22 14:2548.6Noon to 3 PM1414-Dec-22 23:1615.89 PM to Midnight1513-Dec-22 12:1450.8Noon to 3 PM1614-Dec-22 11:2027.79 AM to Noon1714-Dec-22 5:2233.73 AM to 6 AM1814-Dec-22 13:5625.1Noon to 3 PM1913-Dec-22 11:2651.69 AM to Noon2014-Dec-22 7:5831.16 AM to 9 AM2114-Dec-22 22:3116.59 PM to Midnight10dCell FormulasRangeFormulaD2:D21D2=XLOOKUP(MOD(A2,1),$E$2:$E$9,$F$2:$F$9,,-1)


Really? That's the same exact thing except you didn't use a Table which not only does it make very clear what's going on, and expand/contract as needed with no change in formulas, but also prevents cell locking mistakes. There are few reasons to NOT use a full Excel Table instead of flat addresses.


----------



## Dave Patton (Dec 17, 2022)

If you prefer, you can use a Table, Name the range of lookup information, or name the array of lookup information.

The Lookup information is not the same.  Post #5 has the category "Midnight to 3 AM" twice.


Why do 3:27 and 5:22 AM show as Midnight to 3 AM?


----------



## farhan11941234 (Dec 15, 2022)

Please help me out to figure out a formula to calculate Date/Time Base Bins (See attached Image).Whenever The ID Creation(Column A) on Current Day and Time 3:00 PM to 6:00 PM, In Column C "> 3 to 6 PM" , Current Date and time between 6 to 7 PM in Column C result should be " > 6 to 7 PM", after 7:00 PM , in Column C should be "> 7:00 PM onwards.
when time > 14 Hours it should be >14 Hours and continue the this logic to >24 and >48 hours.
I would be very thankful to help me devolope the formula for this.

Sorry, for not using the XL2BB becuase of the admin Limitations.

Thanks


----------



## Fluff (Dec 17, 2022)

jdellasala said:


> There are few reasons to NOT use a full Excel Table instead of flat addresses.


That  is you opinion, IMO there are lots of reason to NOT use tables.


----------



## jdellasala (Dec 17, 2022)

Fluff said:


> That  is you opinion, IMO there are lots of reason to NOT use tables.


That is not just my opinion, it's a fact.

A fact that using Table references make a formula much clearer.
A fact that as data is added to a Table the ranges grow with it.
If it's just a straight table with rows and columns of data, not taking advantage of Excel's Table feature is not something that someone who considers accuracy would do.

The only reasons to NOT use a table is the format does not lend itself to a Table format - as in Financial Statements, OR when a Spilled Array is used as it cannot be used in an Excel Table.

I was just trying to point out GOOD Excel practice. The opinion that there are "LOTS" of reasons is misguiding, and as the audience tends to have less experience, I think it's better to advocate good practices.

But go ahead, list your "LOTS" of reasons. I'm interested in seeing them.


----------



## Fluff (Dec 17, 2022)

jdellasala said:


> That is not just my opinion, it's a fact.


I would have to strongly disagree with this statement. I re-iterate it is just your opinion.



jdellasala said:


> But go ahead, list your "LOTS" of reasons. I'm interested in seeing them.


Not in this thread as it is not relevant to the original question.


----------



## farhan11941234 (Dec 17, 2022)

Snapshot attached of the formula, Please suggest if the same can be acheived without using XLOOKUP for older version of Excel.


----------



## jdellasala (Dec 17, 2022)

farhan11941234 said:


> Snapshot attached of the formula, Please suggest if the same can be acheived without using XLOOKUP for older version of Excel.


VLOOKUP will do the same thing, it's just a bit more cumbersome to use. INDEX/MATCH will also work.


----------



## farhan11941234 (Dec 17, 2022)

if you could plesse share the Vlookup or INDEX/MATCH Version of it.


----------



## Dave Patton (Dec 17, 2022)

Time.xlsmABCDEF1Time StampHoursTimeCategory214-Dec-22 0:1238.8Midnight to 3 AM0Midnight to 3 AM314-Dec-22 3:2735.63 AM to 6 AM03:003 AM to 6 AM413-Dec-22 17:2345.73 PM to 6 PM06:006 AM to 9 AM514-Dec-22 4:4834.23 AM to 6 AM09:009 AM to Noon613-Dec-22 17:3745.43 PM to 6 PM12:00Noon to 3 PM714-Dec-22 5:2233.73 AM to 6 AM15:003 PM to 6 PM814-Dec-22 13:5625.1Noon to 3 PM18:006 PM to 9 PM913-Dec-22 6:0051.63 AM to 6 AM21:009 PM to Midnight1010dCell FormulasRangeFormulaD2:D9D2=LOOKUP(MOD(A2,1),$E$2:$F$9)

VLookup
Time.xlsmABCDEF1Time StampHoursTimeCategory214-Dec-22 0:1238.8Midnight to 3 AM0Midnight to 3 AM314-Dec-22 3:2735.63 AM to 6 AM03:003 AM to 6 AM413-Dec-22 17:2345.73 PM to 6 PM06:006 AM to 9 AM514-Dec-22 4:4834.23 AM to 6 AM09:009 AM to Noon613-Dec-22 17:3745.43 PM to 6 PM12:00Noon to 3 PM714-Dec-22 5:2233.73 AM to 6 AM15:003 PM to 6 PM814-Dec-22 13:5625.1Noon to 3 PM18:006 PM to 9 PM913-Dec-22 6:0051.63 AM to 6 AM21:009 PM to Midnight1010dCell FormulasRangeFormulaD2:D9D2=VLOOKUP(MOD(A2,1),$E$2:$F$9,2,1)


----------



## jdellasala (Dec 17, 2022)

I finally got it! Three versions too.
Book1GIJKLMN1Time StampXcatVcatIcatTimeCategory212/14/22 12:12 AMMidnight to 3 AMMidnight to 3 AMMidnight to 3 AM12:00:00 AMMidnight to 3 AM312/13/22 3:33 PM3 PM to 6 PM3 PM to 6 PM3 PM to 6 PM02:59:00 AMMidnight to 3 AM412/14/22 7:56 AM6 AM to 9 AM6 AM to 9 AM6 AM to 9 AM03:00:00 AM3 AM to 6 AM512/13/22 6:08 PM6 PM to 9 PM6 PM to 9 PM6 PM to 9 PM05:59:00 AM3 AM to 6 AM612/13/22 6:59 PM6 PM to 9 PM6 PM to 9 PM6 PM to 9 PM06:00:00 AM6 AM to 9 AM712/13/22 9:38 AM9 AM to Noon9 AM to Noon9 AM to Noon08:59:00 AM6 AM to 9 AM812/13/22 1:48 AMMidnight to 3 AMMidnight to 3 AMMidnight to 3 AM09:00:00 AM9 AM to Noon912/14/22 3:27 AM3 AM to 6 AM3 AM to 6 AM3 AM to 6 AM11:59:00 AM9 AM to Noon1012/13/22 5:23 PM3 PM to 6 PM3 PM to 6 PM3 PM to 6 PM12:00:00 PMNoon to 3 PM1112/14/22 4:48 AM3 AM to 6 AM3 AM to 6 AM3 AM to 6 AM02:59:00 PMNoon to 3 PM1212/13/22 5:37 PM3 PM to 6 PM3 PM to 6 PM3 PM to 6 PM03:00:00 PM3 PM to 6 PM1312/13/22 2:25 PMNoon to 3 PMNoon to 3 PMNoon to 3 PM05:59:00 PM3 PM to 6 PM1412/14/22 11:16 PM9 PM to Midnight9 PM to Midnight9 PM to Midnight06:00:00 PM6 PM to 9 PM1512/13/22 12:14 PMNoon to 3 PMNoon to 3 PMNoon to 3 PM08:59:00 PM6 PM to 9 PM1612/14/22 11:20 AM9 AM to Noon9 AM to Noon9 AM to Noon09:00:00 PM9 PM to Midnight1712/14/22 5:22 AM3 AM to 6 AM3 AM to 6 AM3 AM to 6 AM11:59:00 PM9 PM to Midnight1812/14/22 1:56 PMNoon to 3 PMNoon to 3 PMNoon to 3 PM1912/13/22 11:26 AM9 AM to Noon9 AM to Noon9 AM to Noon2012/14/22 7:58 AM6 AM to 9 AM6 AM to 9 AM6 AM to 9 AM2112/14/22 10:31 PM9 PM to Midnight9 PM to Midnight9 PM to MidnightSheet2Cell FormulasRangeFormulaI2:I21I2=XLOOKUP(TIME(HOUR(G2),MINUTE(G2),0),TimeCats[Time],TimeCats[Category],"#####",1)J2:J21J2=VLOOKUP(TIME(HOUR([@[Time Stamp]]),MINUTE([@[Time Stamp]]),0),TimeCats,2)K2:K21K2=INDEX(TimeCats[Category],MATCH(TIME(HOUR([@[Time Stamp]]),MINUTE([@[Time Stamp]]),0),TimeCats[Time],1))
I looked at this using a sequence of 288 five minute time intervals with all 3 formulas, and it worked there too. Of note, INDEX-MATCH did NOT like a spilled array!
Anyway, hope that does it!


----------



## jdellasala (Dec 17, 2022)

Dave Patton said:


> Time.xlsmABCDEF1Time StampHoursTimeCategory214-Dec-22 0:1238.8Midnight to 3 AM0Midnight to 3 AM314-Dec-22 3:2735.63 AM to 6 AM03:003 AM to 6 AM413-Dec-22 17:2345.73 PM to 6 PM06:006 AM to 9 AM514-Dec-22 4:4834.23 AM to 6 AM09:009 AM to Noon613-Dec-22 17:3745.43 PM to 6 PM12:00Noon to 3 PM714-Dec-22 5:2233.73 AM to 6 AM15:003 PM to 6 PM814-Dec-22 13:5625.1Noon to 3 PM18:006 PM to 9 PM913-Dec-22 6:0051.63 AM to 6 AM21:009 PM to Midnight1010dCell FormulasRangeFormulaD2:D9D2=LOOKUP(MOD(A2,1),$E$2:$F$9)
> 
> VLookup
> Time.xlsmABCDEF1Time StampHoursTimeCategory214-Dec-22 0:1238.8Midnight to 3 AM0Midnight to 3 AM314-Dec-22 3:2735.63 AM to 6 AM03:003 AM to 6 AM413-Dec-22 17:2345.73 PM to 6 PM06:006 AM to 9 AM514-Dec-22 4:4834.23 AM to 6 AM09:009 AM to Noon613-Dec-22 17:3745.43 PM to 6 PM12:00Noon to 3 PM714-Dec-22 5:2233.73 AM to 6 AM15:003 PM to 6 PM814-Dec-22 13:5625.1Noon to 3 PM18:006 PM to 9 PM913-Dec-22 6:0051.63 AM to 6 AM21:009 PM to Midnight1010dCell FormulasRangeFormulaD2:D9D2=VLOOKUP(MOD(A2,1),$E$2:$F$9,2,1)




```
LOOKUP(MOD(A2,1)
```
 Sweet!


----------



## farhan11941234 (Dec 18, 2022)

Thanks for other versions, but Vlookup and lookup version not working for me for unknown reason. I am trying to figure it why VLOOKUP is not working.


----------



## farhan11941234 (Dec 15, 2022)

Please help me out to figure out a formula to calculate Date/Time Base Bins (See attached Image).Whenever The ID Creation(Column A) on Current Day and Time 3:00 PM to 6:00 PM, In Column C "> 3 to 6 PM" , Current Date and time between 6 to 7 PM in Column C result should be " > 6 to 7 PM", after 7:00 PM , in Column C should be "> 7:00 PM onwards.
when time > 14 Hours it should be >14 Hours and continue the this logic to >24 and >48 hours.
I would be very thankful to help me devolope the formula for this.

Sorry, for not using the XL2BB becuase of the admin Limitations.

Thanks


----------



## jdellasala (Dec 18, 2022)

farhan11941234 said:


> Thanks for other versions, but Vlookup and lookup version not working for me for unknown reason. I am trying to figure it why VLOOKUP is not working.


I didn't save the original work, so I copied it from here. WHAT A MESS! Formulas and even values came in as text. It needs a lot of work, but mostly just selecting a cell, hitting F2 to edit the formula/value, and then hitting [Enter] to get it back to where it needs to be!


----------



## Dave Patton (Dec 18, 2022)

Try downloading the Vlookup version from post #17 to a clean sheet.

Click on the icon below the f(x) in the header, move to your sheet, and paste.
You can review the formula with Formulas Evaluate Formula.


----------

