Help: variation on *Day N of X Days*

  • Thread starter Thread starter Graystar
  • Start date Start date
G

Graystar

Hi,
Trying to find a simpler way to
find the workday number from a list:

Problem description:
An input plugin date field [Today's weekday Workdate]

A List in 2 columns are the 20 *workdays (M-F only less holidays) in Feb
(1-20)
where Feb 2 = 1, Feb3 = 2, etc in 2 columns
as in [Date] & [Day#]

Details:
[Day#] column:
A list of numbers, 1 to 23,
23 being the max # of workdays in any given month (all less holidays &
weekends).

[Date] column:
When the date fill column changes month, conditional formatting changes the
text to white as to not be visible.

So, how best to compare the input date to the list and pull the [Day#]?

Thanks.
Chris
 
Hi Chris
not quite sure what you're trying to achieve. Could you post an example
(as plain text - no attachment) which describes your desired result.
Currently this sound like a customer format in combination with WORKDAY
could do what you're trying to achieve
 
Thanks, got it today.
Graystar's Answer:
Automatic Workday Number of Total Work Days in Month

Problem Description:
Day X of Max Days
Plug Report date: 5/3/04 (Workday week only)

Express the plugin day as the Nth workday of the Max number of NetWorkdays
in the Plug day Month

Answer:
Day X of Max Days =
"DAY "&
LOOKUP($F$1,A4:A26,B4:B26)&
" OF "&
NETWORKDAYS(EOMONTH($F$1,-1)+1,EOMONTH(F1,0),'C:\path
info\[Holidays.xls]Sheet1'!$D$5:$D$10)&
" DAYS"

J1 is where I put the DXoMD equation above

$F$1 is the reference cell for the <plug> date of the report.

The column of dates below are located at: A4:A26

The plain list of numbers 1 to 23 are at: B4:B26

DATE
05/03/04 1
05/04/04 2
05/05/04 3
05/06/04 4
05/07/04 5
05/10/04 6
05/11/04 7
05/12/04 8
05/13/04 9
05/14/04 10
05/17/04 11
05/18/04 12
05/19/04 13
05/20/04 14
05/21/04 15
05/24/04 16
05/25/04 17
05/26/04 18
05/27/04 19
05/28/04 20
05/31/04 21
*06/01/04*22 I put the "*" in this post to indicate that I used
conditional formatting to "whiteout" the months
*06/02/04*23 out of the month date range of the plug date

EOM 21 <= you can figure out the equation I used for this from the
equation above

The idea was to automate some of the mundane headers and info in a sheet I
work with at work <not mine>.
It's awful.
So, to make it tolerable I automated some of it.

Here is what plugging in the Report Date at F1 does:
1) Changes titles that refer to months
2) Auto fills the Workdays of the month less the holidays (holidays in a
holiday.xls file)
3) Changes this Day X of Max Days thingy (04/05/04 = Day 3 of 22 Days) for
April

4) <pending> I'm still working on having the plugin date change the
equations to pull data from the previous months
equivalent workday <calculated in 3) above>.
It's such a annoyance to change those equations every day <or forget to>.

Hope this helps someone.

Chris H.
Graystar CAH
Informal gallery at: http://home.flash.net/~graystar
Moderator for Assemblers of Infinity on Annexcafe
 
Back
Top