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