Jun 14 2018
05:21 AM
- last edited on
Jul 31 2018
08:27 AM
by
TechCommunityAP
Jun 14 2018
05:21 AM
- last edited on
Jul 31 2018
08:27 AM
by
TechCommunityAP
I am working on a timekeeping worksheet that keeps track of anticipated vacation time. For each 7 hours of overtime worked, the time is kept in a "bank" to be used for vacation days. 7 hours of overtime equals one day of vacation. The formula looks at counted time and will mark each "accumulated" vacation day with an X when it reaches integers of 7. Since the values of accumulated time are not whole numbers, the formula has to take that into consideration and not lose time.
Jun 14 2018 06:20 AM - edited Jun 14 2018 06:21 AM
Hi Renee,
Please try this formula in column H.
=IF(OR(D1>=7,D1>=14,D1>=21),"X","")
I hope I understand you well!
Regards
Jun 14 2018 07:41 AM
Jun 14 2018 08:44 AM - edited Jun 14 2018 08:44 AM
Hi Renee,
Please note that formula returns just single X if values in column D go higher than 7 or 14 or 21.
If you want it to return more that one X based on the number, so please try this instead:
=IF(D1>=21,"XXX",IF(D1>=14,"XX",IF(D1>=7,"X","")))
It will give you a result like this:
Please test it, and tell me what you think.
And if you want to adjust the formula so that it will return more than XXX, please provide more details about that because the above formula will return XXX if the value in column D is greater than or equal to 21.
Regards
Jun 14 2018 02:27 PM
It did not quite work but it got me playing !!! :)
I also changed it to D3, since D3 is the first cell with a numerical value (D1 is a title in my example).
I tried a range, D3:D10
=IF(OR(D3:D10>=7,D3:D10>=14,D3>=21),"X","")
It's not working :(
I am trying to get it to simply put an "X" in each cell in Column H to show each time a multiple of 7 is reached in column D.
Cell H3 is first. 7 = X in H3.
14 = X in H4.
21 = X in H5.
28 = X in H6.
35 = X in H7.
But we can't look at whole numbers because it skips from (for example) 6.75 to 7.75 in cells D7 and D8. The .75 can not be lost.
I hope that makes sense :)
Jun 14 2018 09:57 PM
Hi Renee,
Sorry about the late reply!
I think I figured out what exactly you're trying to do.
So please try this formula:
=IF(IF((SUMPRODUCT(OR($D$3:$D$10>=21)+OR($D$3:$D$10>=14)+OR($D$3:$D$10>=7)+OR($D$3:$D$10>=28)+OR($D$3:$D$10>=35)))-ROW(A1)+0.5<0,0,1),"X","")
Try to delete 40 in cell D10, and see how it works!
If you want the result to be in a single cell, you can depend on this:
=REPT("X",SUMPRODUCT(OR($D$3:$D$10>=21)+OR($D$3:$D$10>=14)+OR($D$3:$D$10>=7)+OR($D$3:$D$10>=28)+OR($D$3:$D$10>=35)))
I know that the formulas are quite complicated, but I didn't figure out a solution easier than this.
However, please find all this in the attached file.
Regards
Jun 15 2018 05:40 AM
Hi!!
Thank you for this. I tried the formula and it works in H3, but it repeats in every other cell H4 through H7. But there should be no X in H4 until the values in D3 to D9 reach 14; No X in H5 until the values in D reach 21, No X in H6 until the values in D reach 28, and No X in H7 until the values in D reach 35.
I played around with your formula and I can't figure it out either. Uch! :)
Jun 15 2018 06:06 AM
Hi Renee,
On my own sheet, it works just fine as the below screenshot.
So, please check each of the following:
Jun 15 2018 06:23 AM
It does work! This is really great. I have to take it apart and understand how this works. I really appreciate it!
One last thing - you mentioned that anything after D9 has to be blank.
But this is meant to be a running spreadsheet. What do we do?
Can we have an option where it will just keep going? Every time a multiple of 7 is reached in Column D, it places another X in column H... ?
Thank you!
Jun 15 2018 06:44 AM
Hi,
Not exactly anything after D9 has to be blank, it just the non-numeric data.
The formula already will just keep going so that every time a multiple of 7 is reached in Column D, it places another X in column H, but this in case if the new value in column D is within the range specified in the formula.
For example, in the formula I suggested the range is D3:D10, so any value within this range, the formula will take it into account.
You can expand the range as you want.