CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
MMsmithH
MMsmithH

Reputation: 447

Formatting and organizing time entries in Google Sheets

I have tables with Start and End time data entered in either an AM/PM cell for each day in this spreadsheet. How do I organize and display the number of hours between the two times to end up with a graph that looks like the second image below?

Starting data

Starting data

Desired data

Desired data

Challenges/variables:

  1. Data is provided in free text so it may not be in a time format. Usually it is 1, 2, 4 or 5 characters but it might be anywhere from 1 -5 characters with some variations depending on the use of a colon. For example, 1, 10, 130, 1:30, 1130, 12:30.

My approach

IF(RIGHT(B2,2)="AM",text(IF(AND(LEN(C2)>0,LEN(C2)<3), 
C2&":00",IF(LEN(C2)>3, C2, "")), "HH:MM AM/PM"),
IF(RIGHT(B2,2)="PM",text(IF(AND(LEN(C2)>0,LEN(C2)<3), 12+C2&":00",IF(LEN(C2)>3,
12+split(C2,":")&":"&RIGHT(C2,2), "")), "HH:MM AM/PM"),""))

I used length to figure out if something was already in a time format, and convert those that weren't. I added 12 to any PM value. If it was blank, it would result in a blank space.

Limitations:

  1. I couldnt get it to work as an array (not sure why)
  2. I realized afterwards that this would not work for 3 or 4 characters where there was no colon Entries like 1120, 145 wont work, for example 7:00 - 1120 = 7:00.

Possible solutions? 1.I think probably a REGEX would work better to convert anything into a time format but other solutions would be possible such as seeing if the 3rd character from the Right was equal to : 2. As far as the reorganization, I think it will require some combination of filter, transpose, or query, but I think a prerequisite might be getting the conversion to work as an array of some sort.

Thanks in advance for your help. The shared spreadsheet is here.

Upvotes: 1

Views: 106

Answers (2)

tomf
tomf

Reputation: 535

You'll have to add in some more if new time formats are added, but give this a swing

=ARRAYFORMULA(
  TRANSPOSE(
   QUERY(
    QUERY(
     IF(ISBLANK(C2:C),,
      {A2:A,REGEXEXTRACT(B2:B,"(.*) \|"),
       IF(REGEXMATCH(TO_TEXT(C2:C),"AM|PM"),C2:C,
        IF(REGEXMATCH(TO_TEXT(C2:C),":"),--(TEXT(C2:C,"HH:MM")&" "&RIGHT(B2:B,2)),
         IF(LEN(C2:C)<3,--(C2:C&":00 "&RIGHT(B2:B,2)),
          --(REGEXREPLACE(TO_TEXT(C2:C),TO_TEXT(RIGHT(C2:C,2)),"")&":"&RIGHT(C2:C,2)&" "&RIGHT(B2:B,2)))))}),
     "select Col1, Max(Col3)
      where 
       Col3 is not null and
       Col1 is not null
      group by Col1
      pivot Col2
      label Col1 'Day'
      format Max(Col3) 'hh:mm am/pm'"),
    "select Col1, Col3, Col2")))

I also added your duration difference to your sheet.

=ARRAYFORMULA(
  IF(ISBLANK(H9:J9),,
   IF(H11:J11<=H10:J10,
    1+H11:J11-H10:J10,
    H11:J11-H10:J10)))

The ranges will be different, depending on where you have it. It would be best to remove the TRANSPOSE from the first formula so the days run down the column. I included a demo of this in your sheet.

Upvotes: 2

player0
player0

Reputation: 1

see:

=ARRAYFORMULA(IF(ISNUMBER(C2:C15)*(IFERROR(TIMEVALUE(C2:C15), 2)<=1), TIMEVALUE(C2:C15), 
 IF(ISNUMBER(C2:C15)*(IFERROR(TIMEVALUE(C2:C15), 2)=2), 
 IFERROR(TIMEVALUE(C2:C15&":00 "&REGEXEXTRACT(B2:B15, "AM|PM")), 
 TIMEVALUE(REGEXREPLACE(C2:C15&"", "(\d+)(\d{2})$", "$1:$2 ")&REGEXEXTRACT(B2:B15, "AM|PM"))),)))

enter image description here

Upvotes: 1

Related Questions