Forum Discussion
Possible Array Formula?
I have a set of data:
Rows = Job Titles
Columns = Months
Values = Budgeted hours within the month
The hours are per month and per job title which means they can be quite high as we have more than one person in certain roles.
I want to get this data into a format to import into our time and attendance software as a budget - for ease I was importing all entries on the 1st of the month. (The day of the month is irrelevant as we will be looking at this data in monthly views). To import you have to enter start date and start time, it auto calcs the end time and date, however, the system does not handle values over 24 hours as it cannot auto calc that the end date is in a different day.
I want to find a way in excel to read my core data and if its over 24 hours add an extra line to the import file for that job title.
It has been mentioned that possibly an array formula can do this but I wouldn't know where to start with this.....Any suggestions would be much appreciated please.
The first screenshot is the core data. The second is the import example - the one in red is the one that will fail.
sking_89 Another possibility is to use the TOCOL-IFS method to unpivot the data and repeat the applicable rows:
=LET( proj, BudgetData!B1, clnt, BudgetData!B2, table, BudgetData!A4:G10, vals, DROP(table, 1, 1), test1, vals<>0, jobs, TOCOL(IFS(test1, TAKE(DROP(table, 1),, 1)), 2, 1), dates, TOCOL(IFS(test1, DROP(TAKE(table, 1),, 1)), 2, 1), hours, TOCOL(IFS(test1, vals), 2, 1), days, ROUNDUP(hours/24, 0), cols, SEQUENCE(, MAX(days)), offs, cols-1, test2, days>=cols, CHOOSE( SEQUENCE(, 6), proj, clnt, TOCOL(IFS(test2, jobs), 2) & " - " & proj, TOCOL(IFS(test2, dates+offs), 2), 1/1440, TOCOL(IFS(days=cols, hours-offs*24, days>cols, 24), 2) ) )Adjust the range references as needed. Also, depending on how your attendance software interprets the data, you may still run into issues because technically 12:01 AM + 24 hours would be 12:01 AM the next day.
Please see the attached sample workbook, which also contains an additional variation...
- Just to clarify, are you saying that you'd like any lines with greater than 24 hours to be broken up such that it will have as many lines as necessary that are up to 24 hours? So for a row with 60 hours, it would break into 3 rows - 2 rows with 24 hours, and 1 row with 12 hours. Do they each need to have a different start Date and Time?
- Yes, That is correct. Ideally any entries with 0 could be ignored ....but they can also be filtered out on the import so either way is ok.
Yes, they will need a different start date so as not to override the previous entry.- what if you actually exceed 1800 hrs? then even putting 24hrs on each day of a 30day month won't be enough. Could you just use Job Role X like
Director of Solutions Delivery - Test 1
Director of Solutions Delivery - Test 2
etc...
instead on incrementing the days?
