ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Date help please (https://www.excelbanter.com/excel-worksheet-functions/208251-date-help-please.html)

Bryan De-Lara[_2_]

Date help please
 
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

Date help please
 
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:$c29360))

Let me know if this works.

Did the last formula I posted for you work?

Tom

Bryan De-Lara[_2_]

Date help please
 
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.


"TomPl" wrote in message
...
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:$c29360))

Let me know if this works.

Did the last formula I posted for you work?

Tom




All times are GMT +1. The time now is 07:18 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com