0

im looking to count for specific months lets say month 6 and 7 because its holiday .

I have found this function on the net here in very down of page .

    =COUNT(FILTER(A2:A;(A2:A-DAY(A2:A))<=DATE(C1;D1;0);(B2:B-DAY(B2:B))>=DATE(C1;D1;0)))

where C1 is year to search and D1 is the month number to search and i have modified it like that

    =IF(YEAR(H204)=YEAR(J204);
COUNT(FILTER(H204;(H204-DAG(H204))<=DATE(YEAR(H204);6;0);(J204-DAY(J204))>=DATE(YEAR(J204);6;0)))+COUNT(FILTER(H204;(H204-DAG(H204))<=DATE(YEAR(H204);7;0);(J204-DAY(J204))>=DATE(YEAR(J204);7;0))) ; 
      IF(J204="";
         COUNT(FILTER(H204;(H204-DAY(H204))<=DATE(YEAR(H204);6;0);(J204-DAY(TODAY()))>=DATE(YEAR(H204);6;0)))+COUNT(FILTER(H204;(H204-DAG(H204))<=DATUM(YEAR(H204);7;0);(J204-DAG(TODAY()))>=DATE(YEAR(H204);7;0)))+
         COUNT(FILTER(H204;(H204-DAY(H204))<=DATE(YEAR(TODAY());6;0);(J204-DAG(TODAY()))>=DATE(YEAR(TODAY());6;0)))+COUNT(FILTER(J204;(H204-DAY(H204))<=DATE(YEAR(TODAY());7;0);(J204-DAY(TODAY()))>=DATE(YEAR(TODAY());7;0))); 
         COUNT(FILTER(H204;(H204-DAG(H204))<=DATE(YEAR(H204);6;0);(J204-DAY(J204))>=DATE(YEAR(H204);6;0)))+COUNT(FILTER(H204;(H204-DAG(H204))<=DATE(YEAR(H204);7;0);(J204-DAY(J204))>=DATE(YEAR(H204);7;0)))+
         COUNT(FILTER(H204;(H204-DAY(H204))<=DATE(YEAR(J204);6;0);(J204-DAY(J204))>=DATE(YEAR(J204);6;0)))+COUNT(FILTER(H204;(H204-DAY(H204))<=DATE(YEAR(J204);7;0);(J204-DAY(J204))>=DATE(YEAR(J204);7;0)))
         )
          )

because the first formulas is not really right . From this function i wrote it works BUT only when the second column J204="" is empty i want to use TODAYs date .

so i replaced this

  (J204-DAY(J204))    ---> (J204-DAY(TODAY())  in line4 and 5

But i dont know how to replace J204 ?

How can i make this working pls ?

EDIT

to make it clear i have just problem if the column J is empty (No date) so i want instead use TODAY instead of J204 so how can i switch this

   (J204-DAY(J204))  to   TODAY ??
  • you're mixing different programs / languages, which makes it really difficult to help. Please note that only Excel / english is on topic here (either raw translation is better then german(??) function names). – Máté Juhász Sep 15 '16 at 11:07
  • What language is your office running in? You have Year which is Englisch, Datum which might be German but Dag and Antal don't tell me anything. Day and Count maybe? Could be Belgian or Dutch? – Seth Sep 15 '16 at 11:08
  • @MátéJuhász AND seth hahaha im very sorry didnt notice the language , i have changed the language im very sorry – Scooter Daraf Sep 15 '16 at 11:13
  • Please describe exactly what do you need, instead of just referring to something similar and posting your attempt. Questions should be self-explaining and be understandable on their own. – Máté Juhász Sep 15 '16 at 13:34
  • @MátéJuhász i have explained in my edit , i dont know how you didnt understand it .Just want to replace column 2 when its empty to to handle with TODAY . – Scooter Daraf Sep 15 '16 at 14:04
0

J204 is a cell reference. Either put =TODAY() into that cell or change every reference to J204 in that formula to another cell reference or TODAY().

But from the question you're asking and what's being discussed in/on that link I'm not sure your formula does actually what you want to do? It sounds like you'd want the Output "June, July" while the formula attempts to get a count for the months that passed between two dates.

  • yes i want count june and july between first date and second date , and if second date is empty then i want use counting to today – Scooter Daraf Sep 15 '16 at 11:16
  • you mean like that (TODAY()-DAY(TODAY()) – Scooter Daraf Sep 15 '16 at 11:16
  • Yes, at least that would correct for your last line in your question. – Seth Sep 15 '16 at 11:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.