28 day reporting

  • Thread starter Thread starter Steved
  • Start date Start date
S

Steved

Hello from Steved

As of 28th Feb I would like to in VBA put in a count of 28
Days as I have a 12 month calendar, also I would like it
to have a Background Color and a Text Color please.

28 days means next reporting period.

Thankyou.
 
Hello From Steved

Feb 28th is for report 1 ,28 days later ie 27th March is
report 2, 28 days after 27th is report 3 and so on for a
period of 12 months in this case end of Jan which give me
13 reporting periods.
 
Feb 28, 2004 was a Saturday. Let's say you have Sunday in column A, Saturday
in column G, and the Feb 28 is in G10. If you calculate the following formula
=MOD(G10,28) the result is 21.

So you can use conditional formatting: select cells G2 down through the last
row of your calendar. Go to Format/Conditional formatting, select Formula Is
from the first dropdown, and write the formula in the box to the right
=MOD(G2,28)=21. Then select whatever format you like.

Another formula that would work is to note the row in which Feb 28 falls. For
me, it's row 10, so the formula could also be =MOD(ROW(),4)=2
 
Thankyou Myrna
-----Original Message-----


Feb 28, 2004 was a Saturday. Let's say you have Sunday in column A, Saturday
in column G, and the Feb 28 is in G10. If you calculate the following formula
=MOD(G10,28) the result is 21.

So you can use conditional formatting: select cells G2 down through the last
row of your calendar. Go to Format/Conditional formatting, select Formula Is
from the first dropdown, and write the formula in the box to the right
=MOD(G2,28)=21. Then select whatever format you like.

Another formula that would work is to note the row in which Feb 28 falls. For
me, it's row 10, so the formula could also be =MOD(ROW (),4)=2

.
Click to expand...
 
Hi Steved,

I have just finished an invoicing spreadsheet for my company to synchronise
our billing with the local authority's 28 day periods. In our case
Date-MOD(Date-1,28) gave me the last day of the period. We wanted the
invoicing year to start on 1st April and end on 31 March so it kept
re-synchronising itself by splitting the 28 day period spanning 1st April
into two part periods - one in each financial year.

In your case the formula =TODAY()-MOD(TODAY()-21,28) will give you the start
of the current period and =TODAY()-MOD(TODAY()-21,28)+27 the last day.

Note however that if you have it as a rolling 28 day period and not tied to
a specific start date like we are, your start date will progressively get
earlier and earlier ie your 'end of January' is actually a start date of 29
January 2005 and an end date of 26 February 2005

Regards

Sandy
 
Hello Sandy

Yes my Company has 28 Day invoicing also, what I have
done is modify your formula ie =$W$20-MOD($W$20-28,28)+28
as I put it in Conditional Formatting, hence I've put
together a 12 month calendar, my problems is it will work
in most months but because our Year Starts Feb to Jan I
am in a situation where for some reason it will not work
in Feb using the above formula. Can you point me the
right direction on how I might have it working.

Thankyou.
 
Back
Top