Aug 02 2021 05:12 AM
Aug 02 2021 05:12 AM
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 Number||Item Name and Notes|
|01_011_0107_1_1||Assistance with Self-Care Activities - Standard - Weekday Daytime||A 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_1||Assistance with Self-Care Activities - Standard - Weekday Evening||A 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_1||Assistance with Self-Care Activities - Standard - Weekday Night||A 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_1||Assistance with Self-Care Activities - Standard - Saturday||A 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_1||Assistance with Self-Care Activities - Standard - Sunday||A 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_1||Assistance with Self-Care Activities - Standard - Public Holiday||A 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_1||Assistance 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:
Aug 02 2021 08:32 AM
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.
Aug 02 2021 09:02 AM
Aug 02 2021 06:04 PM
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
Aug 03 2021 06:32 AM
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.
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.