Forum Discussion

sking_89's avatar
sking_89
Copper Contributor
Aug 05, 2024

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 th...
BI & Data Analysis
Formulas and Functions
Macros and VBA
Steve_SumProductCom's avatar
Steve_SumProductCom
MVP
Aug 05, 2024
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?
sking_89's avatar
sking_89
Copper Contributor
Aug 05, 2024
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.
  • m_tarler's avatar
    m_tarler
    Bronze Contributor
    Aug 05, 2024
    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?
    • sking_89's avatar
      sking_89
      Copper Contributor
      Aug 05, 2024
      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
      • m_tarler's avatar
        m_tarler
        Bronze Contributor
        Aug 05, 2024

        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)<>"","")
)

Resources