Solved How to write a time calculation plus text referencing

March 3, 2018 at 15:25:00
Specs: Windows 10
I am working on a timesheet spread. One column is for time in, the next for time out, the 3rd for how many hours worked. In the first column, an employee can input "Holiday" or "Standby", worth 8 hours and 6 hours. I am trying to write a formula that will look at the first cell and if it is time, calculate the hours worked, if it is not, search the list for Holiday or Standby and if found, display the hours they are worth. If no text or number is entered, remain blank. I have been working on this formula for several days and can't get it to work correctly.

=IF(ISNUMBER(Q14),AF14,IF(Q14=Type_of_Pay,Hours))

=IF(ISNUMBER(Q14),AF14) ** This works**

AF14=TEXT(W13-Q13, "h") **This works**

IF(Q14=Type_of_Pay,Hours)) *****This part does not work, Type_of_Pay and Hours are the names of 2 groups that are just:

Holiday 8
Standby 6

Thank you for your help.

message edited by RgChk


See More: How to write a time calculation plus text referencing

Report •

#1
March 3, 2018 at 17:52:07
✔ Best Answer
I'm a bit confused.

What do you mean by "names of 2 groups"? Do you mean Named Ranges? If so, writing an IF function to check for a Named Range doesn't make sense.

I don't have access to Excel right now, so I can't test this, but I think something like this might work (in concept, at least). I might be missing a comma or have a parenthesis out of place.

=IF(Q14="","",IF(ISNUMBER(Q14),AF14,IF(Q14="Holiday",8,6)))

Basically what I'm going for is....

If Q14 equals Nothing, return Nothing. If Q14 is a number, return AF14. If Q14 equals Holiday, return 8, if not, return 6, since that's the only answer left.

If you get the syntax right, that should get you the result you asked for.

However, I'm not really sure why you are using AF14 in the manner that you are. Why not do the Time calculation inside the IF function? The way you doing it, isn't AF14 going to produce an error if there isn't a numerical value in the cells you are referencing in the formula.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Report •
Related Solutions


Ask Question