Possible Array Formula?

[sking_89]

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.

[#2]

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?

[#3]

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.

[#4]

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?

[#5]

We are unlikely to exceed monthly hours as we have several projects running at one time and time is split across them all. The import will be split across the projects so the import value will be different due to its 'Tag' and therefore not overwrite an entry on the same date for that role.

I could use the format you suggested, but the import template would need to be huge to account for 50 odd job roles having mulitple monthly entries in the same project....this would lead to potential errors so I would prefer a more automated method

[#6]

@sking_89  well it isn't pretty and maybe one of those new pivotby or groupby function would have helped but here is an option (note I formated the input date as a table but didn't really use it that way...

=LET(data, DataTable[#All],
title,{"Project","Client","Description","Start Date","Start Time","Duration (h)"},
proj, ProjName,
client,ClientName,
mon, EDATE(B1,0),
tim, TIME(0,1,0),
desc, DROP(TAKE(data,,1),1),
dur, --DROP(FILTER(data,IFERROR(--(TAKE(DataTable[#All],1)),0)=mon,""),1),
_RR, SEQUENCE(ROWS(dur)),
maxN, INT(MAX(dur)/24),
out, REDUCE(title,_RR,LAMBDA(_p,_q,
      LET(_d, INDEX(dur,_q), _n,INT(_d/24),VSTACK(_p,
             HSTACK(proj, client, INDEX(desc,_q), mon, tim, MOD(_d,24)),
             IF(_n,
                      DROP(REDUCE("",SEQUENCE(maxN),LAMBDA(_r,_s,VSTACK(_r,HSTACK(proj, client, INDEX(desc,_q), mon + _s, tim, 24)))),1),
                 "")
             ))
   )),
FILTER(out,TAKE(out,,-1)<>"","")
)

[#7]

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