Excel formula for categorising dates & times into billing codes

Copper Contributor

Hi

I am trying to convert timesheet data into billable codes according to the following rules.  Any assistance would be greatly appreciated!  I have attached the spreadsheet with sample timesheet data and lookup codes

Item NumberItem Name and Notes 
01_011_0107_1_1Assistance with Self-Care Activities - Standard - Weekday DaytimeA Weekday Daytime Support is any support to an individual participant that starts at or after 6:00 am and ends before or at 8:00 pm on a single weekday (unless that support is a Public Holiday Support or a Night-time Sleepover Support).
01_015_0107_1_1Assistance with Self-Care Activities - Standard - Weekday EveningA Weekday Evening Support is any support to an individual participant that starts after 8:00 pm and finishes at or before midnight on a single weekday (unless that support is a Public Holiday Support or a Night-time Sleepover Support).
01_002_0107_1_1Assistance with Self-Care Activities - Standard - Weekday NightA Weekday Night Support is any support to an individual participant that commences at or before midnight on a weekday and finishes after midnight on that weekday, or commences before 6:00 am on a weekday and finishes on that weekday (unless that support is a Public Holiday Support, Saturday Support, Sunday Support or a Night-time Sleepover Support).
01_013_0107_1_1Assistance with Self-Care Activities - Standard - SaturdayA Saturday Support is any support to an individual participant that starts at or after midnight on the night prior to a Saturday and ends before or at midnight of that Saturday (unless that support is a Public Holiday Support or a Night-time Sleepover Support).
01_014_0107_1_1Assistance with Self-Care Activities - Standard - SundayA Sunday Support is any support to an individual participant that starts at or after midnight on the night prior to a Sunday and ends before or at midnight of that Sunday (unless that support is a Public Holiday Support or a Night-time Sleepover Support).
01_012_0107_1_1Assistance with Self-Care Activities - Standard - Public HolidayA Public Holiday Support is any support to an individual participant that starts at or after midnight on the night prior to a Public Holiday and ends before or at midnight of that Public Holiday (unless that support is a Night-time Sleepover Support).
01_010_0107_1_1Assistance with Self-Care Activities - Night-Time Sleepover

A Night-time Sleepover Support is any support to an individual participant delivered on a weekday, a Saturday, a Sunday or a Public Holiday that:
o  commences before midnight on a given day and finishes after midnight on that day; and
o  is for a continuous period of eight (8) hours or more; and
o  the worker is allowed to sleep when they are not providing support.

 

  

 

 

 

 

 

 

 

 

 

4 Replies

@Vanessa000 

 

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".

mathetes_0-1627917782895.png

 

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.

@Vanessa000 

In addition to @mathetes comment. You say "I am trying to convert timesheet data into billable codes" - could you show what you try to do, even if result is not correct. Or you'd like someone from us does this project from scratch?

Hi @Sergei Baklan  and @mathetes 

Thanks for the feedback.  I have attached a manually completed timesheet to show what I am trying to 'automate'.  My plan is to download timesheet data into a workbook and have a new sheet that converts the data in each timesheet row into a billing item number based on days & time worked.  I thought I could achieve this via nested IF/AND statements, but couldn't make it work.

 

Unfortunately, as convoluted as they are, I can't change the billing codes (AU government-issued). The descriptions for the use of the codes are outlined in the National Disability Insurance Scheme (NDIS) price arrangements and limits guide- https://www.ndis.gov.au/media/3374/download

 

 

@Vanessa000 

 

That new spreadsheet has a totally different format for the dates and times; different, that is, from the first one that you posted. In the first, you had start dates and start times, as well as end dates and end times. In these it doesn't appear that any different end dates exist, that entries that might have either begin or end at midnight rather than flipping over from one day to the next. It also doesn't look like you've covered every possibility in terms of the codes.

 

Frankly, I find it a bit odd that each of the lines that you've coded as eligible for "Overnight Allowance" begin at midnight and end at 6 a.m.  It sounds more like people retro-fitting times to meet criteria, rather than posting actual times (humans just aren't THAT precise in their real times of checking in and out).

 

In any event, to the extent that you might want to resolve this on your own, given the convoluted nature of the codes and the conditions that underlie the codes, I would suggest you NOT try to write a single formula to include all of the calculations and comparisons along with ANDs nested in IFs, which probably would be possible but would end up being virtually unintelligible (kind of like this sentence). Seriously, the more you can simplify the better for those who come after you. 

 

Instead:

  • You can create a single field that includes both date and time for start and finish. I've attached an example file.
  • Then, with that, you can create what are often called "helper columns" that, one-by-one, test for each possible condition, and produce a simple result. For example:
    • =IF(EndDate=StartDate,"Y","N")
    • Subtract Start from End to get number of hours (multiply the resulting fraction of a day by 24 to get hours)
    • Use WEEKDAY function to determine what day of week

THEN, with those as the basis for your various conditions, you should be able to write an IF (or an IFS) statement that looks at the various conditions and determines the appropriate codes. I'm going to leave that to you--since you understand your codes--or somebody more interested in the minutia than I. I've created the helper columns based on your first posting.