Date help please

[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.

 

[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

 

[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.