excelexcel-formula

Calculate hours based on individual date/month

dataxx

Column 1 Column 2 Column 3 Column 4 Column 5 Column 6 Column 7 Column 8
01-Oct-24 01-Oct-24 01-Oct-24 01-Oct-24
02-Oct-24 02-Oct-24 02-Oct-24 02-Oct-24
03-Oct-24 03-Oct-24 03-Oct-24 03-Oct-24
04-Oct-24 1:01 04-Oct-24 04-Oct-24 7:14 04-Oct-24
05-Oct-24 1:30 06-Oct-24 5:29 06-Oct-24 1:01 06-Oct-24
07-Oct-24 07-Oct-24 07-Oct-24 07-Oct-24
08-Oct-24 5:28 08-Oct-24 08-Oct-24 5:26 08-Oct-24
09-Oct-24 09-Oct-24 09-Oct-24 09-Oct-24
10-Oct-24 10-Oct-24 10-Oct-24 10-Oct-24
11-Oct-24 1:00 11-Oct-24 11-Oct-24 6:00 12-Oct-24 7:00
13-Oct-24 6:30 13-Oct-24 14-Oct-24 6:30 15-Oct-24 0:30

Cannot add hours based on individual date/month due to date in multiple column.

How can i get the following output in excel using function?

output

enter image description here

and

enter image description here

Thanks

Solution

  • You could try something along the lines of using GROUPBY()

    enter image description here

    • Output Option One:

    =LET(
     α, WRAPROWS(TOCOL(A1:H11),2),
     δ, DROP(α,,1),
     GROUPBY(TAKE(α,,1),δ,SUM,,0,,δ<>""))

    Or, using MAP()

    =LET(
     α, WRAPROWS(TOCOL(A1:H11),2),
     δ, TAKE(α,,1),
     φ, DROP(α,,1),
     ε, UNIQUE(δ),
     Σ, MAP(ε, LAMBDA(a, SUM(N(a=δ)*φ))),
     FILTER(HSTACK(ε,Σ),Σ<>0))

    • Output Option Two:

    enter image description here

    =LET(
     α, WRAPROWS(TOCOL(A1:H11),2),
     δ, DROP(α,,1),
     GROUPBY(TEXT(TAKE(α,,1),"mmm-yy"),δ,SUM,,0,,δ<>""))

    =LET(
     α, WRAPROWS(TOCOL(A1:H11),2),
     δ, TEXT(TAKE(α,,1),"mmm-yy"),
     φ, DROP(α,,1),
     ε, UNIQUE(δ),
     Σ, MAP(ε, LAMBDA(a, SUM(N(a=δ)*φ))),
     FILTER(HSTACK(ε,Σ),Σ<>0))

    One should makesure to set the time formatting for the output option 2s as [h]:mm:ss;@