Forum Discussion
Excel formula for categorising dates & times into billing codes
Hi SergeiBaklan 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
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.