how do i automatically update % figures daily

J

johnm

I have a sales figure sheet showing month to date etc ..on this sheet I also
show the % of month gone .. so Sept's got 26 trading days @ 3.85% per day
... how can I get this figure to automatically update each day ( at the moment
I have to do manually) .. . so that day 2 = 7.7% ... day 3 = 11.55% ...
until last day 100%

Many thanks
John
 
B

Bob Phillips

=NETWORKDAYS(TODAY()-DAY(TODAY())+1,TODAY())/NETWORKDAYS(TODAY()-DAY(TODAY()
)+1,DATE(YEAR(TODAY()),MONTH(TODAY())+1,0))

and format as percentage


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
B

Bob Phillips

By the way, I calculate 21 working days, and therefore trading days, for
September.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
R

Ron Coderre

If "trading days" are Monday thru Saturday, try this:


=SUMPRODUCT(--(WEEKDAY(ROW(INDEX(A:A,TODAY()-DAY(TODAY())+1):INDEX(A:A,TODAY())))<>1))/SUMPRODUCT(--(WEEKDAY(ROW(INDEX(A:A,TODAY()-DAY(TODAY())+1):INDEX(A:A,DATE(YEAR(TODAY()),MONTH(TODAY())+1,0))))<>1))

That formula divides the number of current month-to-date days (excluding
Sundays) by the number of days in the current month (excluding Sundays)

In the case of today (9/11/2006), that is the 9th trading day out of 26
trading days in September: 34.6%

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP
 
B

Bob Phillips

John,

a few questions.

The trading days will already be entered. What cells are they in?

Is the percentage to be a percentage of trading days passed, or a percentage
of some trade figure (I think it is the former, but just to be sure. If it
is the latter, what cells are the trade amounts in)?

Does today count as a day passed, or yet to pass?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Top