I have sales data that gives me dates in a bad format. Every new sale gets automatically added to the sheet. Looks like this:

This will continue on for all sales. Now the date format is UTC for whatever reason and I can't adjust that, so within this formula I have to subtract 6 hours to get it to central time. I'm trying to create an auto-updating chart that shows an average day for 7 days, so I'm trying to do a sumif formula.

Here's what I have on Sheet2:

=sumif(Sheet1!C:C,index(split((index(split(Sheet1!B:B,"T"),1) index(split(left(Sheet1!B:B,19),"T"),2))-0.25,"."),1),A1)

Where A1 is a single date. Testing this with one date and not the range shows that it does match. When I do the range, the total comes to 0, even though multiple different dates should match. What am I doing wrong?

CodePudding user response：

Use regexreplace() and query(), like this:

=arrayformula( 
  query( 
    { 
      weeknum( 
        regexreplace(B2:B, "([-\d] )T(\d\d:\d\d). ", "$1 $2") 
        - 
        "6:00" 
      ), 
      C2:C 
    }, 
    "select Col1, avg(Col2) 
     where Col1 is not null 
     group by Col1 
     label Col1 'week #' ", 
    0 
  ) 
)

CodePudding user response：

I think you're trying to split the values and sum them. I can't understand fully what's the purpose of 19 in LEFT function, and why are you again splitting it? Maybe some approach similar to yours is use LEFT function with 10 characters for the date, and MID from 12th character to get the time. Then substract .25 for the 6 hours as you did, and ROUNDDOWN with 0 digits to get the only the day

=ARRAYFORMULA(ROUNDDOWN(LEFT('Sheet1'!B:B,10) MID('Sheet1'!B:B,12,8)-0.25,0))

And then you can insert it in your SUMIF:

=SUMIF(Sheet1!C:C,ARRAYFORMULA(ROUNDDOWN(LEFT(Sheet1!B:B,10) MID(Sheet1!B:B,12,8)-0.25,0)),A1)