Your usage of CASE() isn't quite right. You don't need to use ISPICKVAL() if your CASE() function is operating on a picklist field.
The syntax is
CASE(
[expression],
[test value 1], [result 1],
[test value 2], [result 2],
...
[test value n], [result n],
[default result]
)
To get the result you want, the first few lines of your CASE should be
CASE(
Time_Spent__c,
"15 min", 15,
"30 min", 30,
...,
0
)
As an interesting exercise, I'll try to explain why your provided snippet was returning 45 for '45 min' and 0 for everything else.
tl;dr:
you managed to create a formula that effectively dynamically changed the formula being evaluated, based on the value of a field
Why was it returning the correct value for '45 min'?
Your first IF(ISPICKVAL()) was being used as the expression that all of the subsequent cases were testing against.
IF(ISPICKVAL(Time_Spent__c, '15 min'), 15, 0) returns either 0 or 15.
Your second and third IF(ISPICKVAL()) were acting as the first test and result. The third and fifth acted as the second test/result, and so on.
For '45 min', the CASE became
CASE(
IF('45 min' == '15 min'), 15, 0),
IF('45 min' == '30 min'), 30, 0), IF(('45 min' == '45 min'), 45, 0),
...
)
which simplifies to
CASE(
0,
0, 45,
...
)
or simply
IF(0 == 0, 45, 0)
Why was this the only non-zero value that would ever be returned?
Simplifying the entirety of your original CASE looks like this
// when Time_Spent__c was '15 min'
CASE(
15,
0, 0,
0, 0,
0, 0,
0, 0,
0, 0,
0
)
// when Time_Spent__c was '30 minutes'
CASE(
0,
30, 0,
0, 0,
0, 0,
0, 0,
0, 0,
0
)
// when Time_Spent__c was '75 minutes'
// with overlapping test values, the first one (0, 0) is used
// ...or maybe the last one is used.
// It couldn't just be going to the default case, or you would have seen
// the proper value for 3 hours
CASE(
0,
0, 0,
0, 75,
0, 0,
0, 0,
0, 0,
0
)
Given those 3 cases above, you should be able to extrapolate why the rest of the values produced a result of '0'.
