Looking for autosum help with numbers, letters and dates

[SlurpeeD]

I am trying to figure out the formula to do an auto sum with numbers letters and times in minutes and seconds.

Its basically a weekly tracker. Cells F4 through L4. I want M4 to add the times (ex 2+39), but ignore the X's which represent days off, and numbers which are their employee attributes (these number 1-46).

Thanks!

 

[myall_blues]

How can we tell the difference between times (2+39) and employee attributes (1-46)?

A picture or some data would help.

 

[SlurpeeD]

7/7/24

7/8/24

7/9/24

7/10/24

7/11/24

7/12/24

7/13/24

Time

Imped

Time

Imped

Time

Imped

Time

Imped

Time

Imped

Time

Imped

Time

Imped

X

2+00

2+39

3+29

1+39

34

X

0

4+26

2+25

X

X

4+01

2+16

3+28

3+38

X

X

3+12

4+36

2+19

2+38

X

X

3+46

19

4+28

1

3+13

3+09

2+54

X

X

34

2+59

4+28

2+44

2+59

3+49

X

X

4+14

1+54

2+15

2+56

X

X

34

25

25

25

25

25

X

X

3+18

3+20

X

X

3+40

4+08

3+33

X

25

25

25

25

25

X

21+19

21+11

13+12

14+00

8+31

17+13

11+07

 

[SlurpeeD]

So each cell will either have an "X", a time represented as "4+26" or "2+25" and so on, or a raw number (1-46). I need a formula that just adds times and ignores everything else. Dont know if that is possible lol.

 

[Peter_SSs]

1. For that sample data, what would be the expected results?
2. Is there also data in the 'Imped' columns? If so, what is that data like (samples)?

 

[SlurpeeD]

There would be a number in the Imped column. Looking for a sum of all the time in each row excluding the regular numbers in the imped column and the X's.

 

[Peter_SSs]

There would be a number in the Imped column.

Thanks for the clarification.

That just leaves ..

For that sample data, what would be the expected results?

 

[myall_blues]

F4 through L4. I want M4 to add the times (ex 2+39), but ignore the X's which represent days off, and numbers which are their employee attributes (these number 1-46).

Also can you clarify - in your original post you say you want the sum from F4 to L4 in M4, but your data as posted would go out to S4?

 

[SlurpeeD]

I would like each row to have a sum of hours represented as either 12:51 or 12+51, ignoring the X's and raw numbers. This sum would be in M4.

 

[Asbestos_Jen]

Try this:

=SUM(IF(ISNUMBER(SEARCH("+",A3:A12)),--REPLACE(A3:A12,2,1,":"),0))