Excel 2003 - Help Please

mercman1969

I wonder if somebody would help me in a small excel problem please.....

I have basically created 2 cells which i want to add a day counter to

Cell 1 is the datum (date in which something is issued)

I want to add a working day counter so that cell 2 shows how many working days have elapsed since the date in cell 1

can this be done?

What would the formula be?


Many Thanks

Sooler

cell2=NETWORKDAYS(TODAY(),cell1)


http://answers.microsoft.com/en-us/office/forum/office_2007-excel/formula-to-count-down-business-days/f4cb0155-421d-4423-9016-6c44a83f9bd9

mercman1969

Doesnt work....

Brings up an error

Can you please confirm

CELL2=NETWORKDAYS(TODAY(),CELL1

I am currently using the following

CELL2 =TODAY()-CELL1

This gives me the required counter, but it counts all days and not just working days

espresso

mercman1969 wrote: »

Doesnt work....

Brings up an error

Can you please confirm

CELL2=NETWORKDAYS(TODAY(),CELL1

I am currently using the following

CELL2 =TODAY()-CELL1

This gives me the required counter, but it counts all days and not just working days

You really should've gone to Specsavers!

If you can't type what has been posted it won't work will it.

Sooler

Yes I can confirm. What I put works, what you put won't work.

mercman1969

I did actually input it correctly into the table

I have tried it again today and although it gives a number, it gives the incorrect number

An instance is shown below

20-Jun-13-32

I cant work out why the number is negitive

what i am trying to achieve is as follows

I issue something that must be completed within 28 working days

I have set up an excel document that i am currently populating manually

I have 3 cells as follows

Cell 1 - Input the date of issue
Cell 3 - Input the date due back (issue + 28 days)

Cell 2 - Working days counter

Sometimes the deadlines are not met and an extension is granted with a revised date entered into cell 4

Ideally i would like for these to self populate with the exception of cells 1 & 4 which have to be manually populated

The days counter i would like to count up from date of issue, but have some way of stopping the count once the item has been received back


Can this be done easily........not very good with excel, and it takes some considerable time to manually populate my table

tarden

The formula is networkdays(start date, end date)
So in your example, it will be =networkdays(Cell 1,today())

mercman1969

Getting more and more confused.....

I have copied the cell structure below...What formula needs to be in each cell?

Cell 1 = Date - Easily done, I just input it manually

Cell 2 = This is the date Counter and needs to begin at date in Cell 1 and stop when date in cell 3 or 4 is reached

Cell 3 = This date is 28 working days from Cell 1

Cell 4 = This date is an extension date and can be 28 days from the date in Cell 3

Can i make it all work with just the input of the issue date being inputted manually?

what formula needs to go into each cell

Mirno

Depends if you need "proper" working days, or just not weekends.

A proper holiday version will require a loop (so you can deal with 2 or more consecutive days holiday - when things get interesting).

If you just want 28 non-weekend days then it's:
=IF(WEEKDAY(<A1,3)>4,"Starting on a non-business day?",IF(WEEKDAY(A1,3)>1,A1+40,A1+38))

Where A1 is your date.
28 working days = 5 weeks + 3 days = 38 days if the start day is a Monday or Tuesday, or 40 days if it's after Tuesday.

Mirno

mercman1969

Mirno wrote: »

Depends if you need "proper" working days, or just not weekends.

A proper holiday version will require a loop (so you can deal with 2 or more consecutive days holiday - when things get interesting).

If you just want 28 non-weekend days then it's:
=IF(WEEKDAY(<A1,3)>4,"Starting on a non-business day?",IF(WEEKDAY(A1,3)>1,A1+40,A1+38))

Where A1 is your date.
28 working days = 5 weeks + 3 days = 38 days if the start day is a Monday or Tuesday, or 40 days if it's after Tuesday.

Mirno

More confused now

I would have thought that each cell would require a seperate formula......

EchoLocation

If you're not concerned with taking into account holidays try the below. Extensions are added as extra days.



If you want to stop the Days Elapsed counter, just add an extra 'Status' cell to which you can add some text to say if it's been completed.
Then just add an IF statement to the 'Days Elapsed' formula.