Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Making a date go red, if date passes todays date. | Excel Worksheet Functions | |||
how to get the random date between the start date and the end date? | Excel Worksheet Functions | |||
Create a formula in a date range to locate a specific date - ecel | Excel Discussion (Misc queries) | |||
Report Date - Date Recv = Days Late, but how to rid completed date | Excel Worksheet Functions | |||
Date updates from worksheet to chart & changes date to a date series! Help!! | Charts and Charting in Excel |