0
I have a calendar of months with time off codes on certain days an employee took off and has scheduled off. Codes are "V" for vacation, "PTO" for Paid Time Off, etc.
I am trying to input a formula for "Vacation Days Used" not scheduled. Here is my argument: Count if the calendar range of B20:R69 contains V, minus V in this range if it greater than today's date. I've spent too much time on this and any assistance would be greatly appreciated.
I have tried:
=COUNTIF(B20:R69,"V")=COUNTIF(B20:R69,"V"<="C12")where C12 represents a cell with =TODAY()
Tiffany
Posted 2018-08-27T17:02:29.440
Reputation: 1
Could you describe what you have already tried? This will make it easier to help you with your issue. – Worthwelle – 2018-08-27T17:09:19.177
So you want to count all the
Von dates less than today? In which range are the dates stored? – Scott Craner – 2018-08-27T17:27:24.280Correct, that's exactly what I need to do. The calendar range is B20:R69. Ahhh, there is no range where the date is stored and I just realized that this is the problem. The calendar range looks just like a calendar with months, days of the week and days, no dates however and this is the problem. – Tiffany – 2018-08-27T17:41:34.027
I have used hidden helper rows for this. In other words, in your calendar insert a row above or below your data that contains your date, either computed or entered. You can hide it after you have it set up. Then you would need to use
COUNTIFSto work with multiple conditions. If you post an image of your calendar spreadsheet (that is update your question with an image) including showing the rows and columns, it would be easier to explain. – Rey Juna – 2018-08-27T18:19:49.973Thank you so much. This is helpful. I will let you know how it turns out. – Tiffany – 2018-08-27T19:37:45.057
I've added the dates above the data. I've hit a road block again. I also can't find the "update your question with an image". I am inputting this formula: =COUNTIFS(B23:R89,"V",B21:R69,"<"&TODAY()) and keep getting the #Value error. The dates are stored above the days of the week. Ranges are: B21:R21, B24:R24, B27:R27, B30:R30, B33:R33, B39:R39, B42:R42, B45:R45, B45:R48, B51:R51, B57:R57, B60:R60; B63:R63, B69:R69, B75:R75, B78:R78, B81:R81; B84:R84, B87:R87. Any guidance would be most appreciated. – Tiffany – 2018-08-28T16:04:47.590
I tried this formula but it's still not working. =COUNTIFS(B21:R21,">="&TODAY(),B23:R23,"=V"). The result is totaling 0, but it should be 2. – Tiffany – 2018-08-28T22:29:15.647
I figured it out: =COUNTIF(IF($B$21:$R$21<TODAY(),$B$23:$R$23),"V"). Thank you all for your help. – Tiffany – 2018-08-29T14:39:10.310
1For future reference, if you are answering someone in the comments you need to put @ and their name (@Rey) to get their attention. I just found your post today. I'm glad you figured it out, but I don't see how this formula would work since it seems to be missing some parameters. However, recommend that you post your own answer to this. That will help future searches, it should allow you to post your image which is always helpful, and it gives people the chance to vote for your answer. – Rey Juna – 2018-09-01T02:32:58.790