Forum Discussion

pcosta01's avatar
pcosta01
Copper Contributor
Feb 01, 2024

Excel Calendar Help

Hello,

Below are two separate sheets. Sheet 1 is the calendar, the non compliant months are for 2023 and compliant months are 2024. Is there a way I can populate sheet 1 with the sheet 2 values, I only want to count the pending rows. The tasks have unique names and hence I want them counted in their respective section on sheet 1. Thanks in advance.

 

excel
Formulas and Functions
  • m_tarler's avatar
    m_tarler
    Bronze Contributor
    Feb 01, 2024

    so the question is missing some information but the answer is yes you can. A formula like:
    =COUNTIFS( Data[Description], [@[Work Order Task]], Data[[Completed Date]], ">"&EOMONTH(C$5,-1), Data[[Completed Date]], "<="&EOMONTH(C$5,0) )
    BUT a couple of things:
    a) the Desciptions must match the Work Order Tasks
    b) row 5 of the calendar uses actual dates like 1/1/2023 for Jan but then you just format those cells to only show the Month (cell format -> custom format -> enter: MMMM )
    you could use other options like DATEVALUE(C$5 & " 1, 2023") but then you have localization issues, less efficient AND you will have to include some extra functions to toggle from 2023 to 2024 but in the above suggestion you just use 1/1/2024 for the Jan on the right half.

    c) the above format assumes the tables are 'format as table' and the table on sheet2 is named Data.  i highly recommend formatting as table so you don't have to worry about your range references being $A$3:$A$100 but then at some point reaching row 101 and not knowing why it broke or using $A$3:$A$100000 and having excel check thousands of blank lines just in case.

Resources