I'm looking to find a way to calculate the number of days our employees are out of the office on work trips. I've read various other threads looking at things like headcount, which are similar, but I can't quite work out how to make it work in this instance.
The differences to the usual lookup vs transactional data problems are that I need to count the number of days that they are away (i.e. between arrival and departure date) and that each day is not always a full day, it may be a half day conference for example. I need the report to be flexible to show how many work days are spent out of the office over any given period.
The only way I can think to do it is to take each day as a different row (e.g. a week trip would be 7 separate rows), but this isn't practical for inputting the data into the spreadsheet
I've attached a sample workbook which will hopefully help explain. Any help is hugely appreciated!