I am trying to create a Sales Report that will show performance vs last year.
I have created a formula to give me yesterdays date with one cell showing the month, one the day and one the year (located in cell C1, D1 and E1 respectively) here are the formulas:
=TEXT(TODAY()-1,"MMMM")
=DAY(TODAY()-1)
=YEAR(TODAY()-1)
Then I have data that looks like this:
[TABLE="width: 1128"]
<colgroup><col width="250" style="width: 188pt;" span="6"> <tbody>[TR]
[TD="width: 250, bgcolor: #002060"]Month[/TD]
[TD="width: 250, bgcolor: #002060"]Day[/TD]
[TD="width: 250, bgcolor: #002060"]Last Year[/TD]
[TD="width: 250, bgcolor: #002060"]This Year[/TD]
[TD="width: 250, bgcolor: #002060"]% Achievement[/TD]
[TD="width: 250, bgcolor: #002060"]Difference[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]January[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: center"]#DIV/0![/TD]
[TD="bgcolor: transparent, align: right"]$0.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]January[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: center"]#DIV/0![/TD]
[TD="bgcolor: transparent, align: right"]$0.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]January[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: center"]#DIV/0![/TD]
[TD="bgcolor: transparent, align: right"]$0.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]January[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: center"]#DIV/0![/TD]
[TD="bgcolor: transparent, align: right"]$0.00[/TD]
[/TR]
</tbody>[/TABLE]
(Data in a separate tab called Sales, table starts at cell A1 and shows the entire year)
I want formulas to return the % achievement and difference for the day (yesterday), Month to Date, and Year to date.
Thank You
I have created a formula to give me yesterdays date with one cell showing the month, one the day and one the year (located in cell C1, D1 and E1 respectively) here are the formulas:
=TEXT(TODAY()-1,"MMMM")
=DAY(TODAY()-1)
=YEAR(TODAY()-1)
Then I have data that looks like this:
[TABLE="width: 1128"]
<colgroup><col width="250" style="width: 188pt;" span="6"> <tbody>[TR]
[TD="width: 250, bgcolor: #002060"]Month[/TD]
[TD="width: 250, bgcolor: #002060"]Day[/TD]
[TD="width: 250, bgcolor: #002060"]Last Year[/TD]
[TD="width: 250, bgcolor: #002060"]This Year[/TD]
[TD="width: 250, bgcolor: #002060"]% Achievement[/TD]
[TD="width: 250, bgcolor: #002060"]Difference[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]January[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: center"]#DIV/0![/TD]
[TD="bgcolor: transparent, align: right"]$0.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]January[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: center"]#DIV/0![/TD]
[TD="bgcolor: transparent, align: right"]$0.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]January[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: center"]#DIV/0![/TD]
[TD="bgcolor: transparent, align: right"]$0.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]January[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: center"]#DIV/0![/TD]
[TD="bgcolor: transparent, align: right"]$0.00[/TD]
[/TR]
</tbody>[/TABLE]
(Data in a separate tab called Sales, table starts at cell A1 and shows the entire year)
I want formulas to return the % achievement and difference for the day (yesterday), Month to Date, and Year to date.
Thank You