Working days/weeks calculation

[colkas]

HI

How can we set a full column so it displays the current amount of working days for this year. This should update each time we oprn the spreadsheet.

Also the same but for weeks for the current year.

Thanks for any help in advance

 

[TonyJollans]

Hi colkas,

Could you be a bit more specific please.

Do you want a count of days, something like "this is working day #208"? This is fairly simple.

Or do you want a cell with the date of each of those days? Whilst possible, this is more involved and it would probably be better to prepopulate your worksheet and use conditional formatting to control which dates were visible.



Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[bluedragon2]

The NETWORKDAYS formula in one of the add-ins will give you working days. Use the NOW() or TODAY() Function in conjunction with it.



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[colkas]

Hi Tonyjollans

Try and explain better

We have a cloumn called working days which we want to base other calculations on.

So depending on which date we open the spreadsheet we want that column to populate with the current working days. We only want working days for the current year. We tried the networkdays function which is okay if we put in manual 2 dates, what we cant work out is how to do it automatcially (01/01/04 to current date)

Hope yu can help

Thanks

 

[TonyJollans]

Hi colkas,

Still not quite sure what you want but this will give you the number of work days from Jan 1 this year (any year) to today ..

[blue][tt] =NETWORKDAYS(DATE(YEAR(TODAY()),1,1),TODAY())[/tt][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[colkas]

Thanks again this works good

Finally how do we do it for nuber of weeks for the year I tried the Weeknum but cant seem to get it to do the same. Any ideas

Thanks again

 

[TonyJollans]

Hi colkas,

If [blue]WEEKNUM(TODAY())[/blue] doesn't work, can you be more precise about what you want.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[colkas]

Ok

Same as before we have a column that needs the number of weeks we are in to date.

So this week we are in week 43 so we need 43 displaying in the column.

Also once this number is in say A1 how do we set it so it poplutes all of column A cells where the other columns are populated. This can be dynamic.

WEEKNUM(TODAY()) did not work

Thanks

 

[TonyJollans]

Hi colkas,

I'm a bit stumped - =weeknum(today()) gives me 43. What does it give you?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[colkas]

Hi

So am I now it works!!!!

Thanks for your help.

 

[colkas]

Hi

Ok now we have the formulas how do we populate a column depending on how many rows come thrugh from the refresh.

We may have 20 rows one time, 10 another etc.... is there a way to make sure the column does the caluclation depending on how many rows each time.

Thanks

 

[TonyJollans]

Hi colkas,

Can't answer at the moment - I have to go out, but what refresh? can you give a bit more detail?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.