Forum Discussion
Excel formula for categorising dates & times into billing codes
You've had a lot of views, but no replies yet. My guess is that your situation still needs more clarification in order for anybody to give the help you're seeking.
Among other things, most of the "Timesheet" data you give in your example file seems straight forward... it might be helpful if you gave (and marked) an example of a timesheet entry that met each of the codes you're seeking to identify.
And if I might ask, are those "Item Number" entries the codes that you are wanting to look up? Is it necessary that they be so abstruse in nature? Even the entries under "Item Name and Notes" are minimally different--i.e., ALL of them begin with the same five words, six if you include the "Standard"--so the only differentiation is the day-and-times of the service.... I can see why it's not easy to decode; the table that's supposed to help do that decoding/encoding needs its own decoding.
Looking at the Timesheet, there's one (highlighted with a purple background) that appears to End before it Starts--and of the three highlighted in yellow that might be thought of as "overnight" only one accounts for eight or more hours; is it the only one that qualifies as truly "overnight".
In any event, you can help us help you if you were to provide a Timesheet example where you've laid out the "correct code" that corresponds, with any notation that might help explain where it might otherwise be ambiguous.