Using Today Function to display certain Cells

Dylan Maddy

I am working on a sheet for salesman to use to track their sales, while setting up reports for our manager to be able to check in and see the YTD sales goals. What i am wanting to do is set up a formula that will show up in his report for him that will show a cell only if the date is Within th month.

 

For example:

If the month is June i need it to display june in my YTD Column

When july comes i need it to automatically display the percentage for june.

 

I was thinking of chaining an IF function that says "Display June YTD % IF the month is June. I need it set up so if it switches to July it will recognize in the formula it is july and change for me.

Ame Rychlak

I have set up a colum to identify the number of the month from the creation date system colum:

=MONTH([Date Created]:[Date Created])

 

I then use that number in my formulas to distinguish months for summary billing for a customer:

=SUMIFS($[Time in Minutes]:$[Time in Minutes], $[Client - Site]:$[Client - Site], "Customer Name", $Billable:$Billable, 1, $[Metric - Month]:$[Metric - Month], 6)

 

There may be an more elegant way to set this up, but this has been working well. And if needed we can manually adjust the month if the project completes in a different month than when it started.

Dylan Maddy

Are you aware of if there is a way to have an IF function to display a specific cell.

Ec: Display ("YTD %") IF Todays date is June then Display Junes YTD Percentage.

 

i haven't been able to figure out if there is a way to put a stipulation if it is to dispay a cell, so that if it changes on my sheet it will show up on my managers sheet. 

Ame Rychlak

It may have to be broken down into different cells with a function on each.

ex:

Date1 is "=TODAY()" to give you today's Date - 6/22/16 - must be in a date column

A2 is "=MONTH($A$1)" to give month number 6

 

You should be able to use the value of A2 in a nested IF statement to to identify the cell where your percentage is.

 

=IF(A2 = 1, [cell for January %], IF(A2 = 2, [cell for February %], IF(A2 = 3, [cell for March %], IF(A2 = 4, [cell for April %], IF(A2 = 5, [cell for May %], IF(A2 = 6, [cell for June %], IF(A2 = 7, [cell for July %], IF(A2 = 8, [cell for August %], IF(A2 = 9, [cell for September %], IF(A2 = 10, [cell for October %], IF(A2 = 11, [cell for November %], IF(A2 = 12, [cell for December %])))))))))))