Date help please

B

Bryan De-Lara

I have a column A5 to A2936 with the date starting 01-Jan-2008 to
10-Jan-2015.
Column B5 is the days of the week.
Column C5 is the working days numbered 1 to 5, missing week ends and public
holidays, non working days up to 254, then the working year starts again at
day 1 for another year and so on.
What I need to do is work out the absences. From another sheet in the
workbook I have the data coming across to give the number of people employed
and the amount of absences. I have the formula for working it all out. The
problem I am having is getting the data from column C into the cell where
that data completes the formula.
I have tried various formulae including =NOW()*(A!C6) & TODAY formulae but
don't seem to be getting very far. I need to know the number of working days
as soon as the date changes, or when it is opened.
Any help would be greatly appreciated. I am learning but at my age the
retention is harder.

Bryan.
 
T

TomPl

Hello Bryan,

It sounds like you are trying to get the number of workdays in the immediate
preceding 365 days from today. Try this:
=SUMPRODUCT(--($A5:$A2936>=TODAY()-365),--($A5:$A2936<=TODAY()),--($c5:$c2936>0))

Let me know if this works.

Did the last formula I posted for you work?

Tom
 
B

Bryan De-Lara

Thank you Tom, it works a treat. A little, no correction, a lot more of a
formula than I was trying, thank you so much.
Only the one problem left and presto I'm a happy chappy. I will have a
workbook that I can use for the next 7 years without having to alter again.

Bryan.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top