Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
recognize weekends and calculate the next working date....
Hello,
I have a spreadsheet where I have certain tasks that must be performed sequentially based on the main task of doing a particular job. If the job happens on the weekend, I need the formula to automatically recognise that it is a weekend and allocate the next working day. (I have put in conditional formatting for public holidays). At present at the top of my spreadsheet I have the number of days after a job when the next task should be done. So the formula in a cell looks like this: $P34+$Q$14, where P34 is the job date, and Q14 is the number of days after when the task can be done. Is there a way I can ensure that the result is a working day, rather than a weekend? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
recognize weekends and calculate the next working date....
Hi Suek
this formula will help you, to look for the next monday, if the result of A1 + B1 is a saturday or a sunday. =IF(WEEKDAY(A1+B1,2)5,A1+B1-WEEKDAY(A1+B1,2)+8,A1+B1) hth Carlo On Nov 19, 1:57 pm, suek wrote: Hello, I have a spreadsheet where I have certain tasks that must be performed sequentially based on the main task of doing a particular job. If the job happens on the weekend, I need the formula to automatically recognise that it is a weekend and allocate the next working day. (I have put in conditional formatting for public holidays). At present at the top of my spreadsheet I have the number of days after a job when the next task should be done. So the formula in a cell looks like this: $P34+$Q$14, where P34 is the job date, and Q14 is the number of days after when the task can be done. Is there a way I can ensure that the result is a working day, rather than a weekend? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate number of WEEKENDS between two dates? | Excel Discussion (Misc queries) | |||
Calculate A Date Excluding Weekends And Holidays | New Users to Excel | |||
Calculate the number of days between two dates,missing weekends? | Excel Worksheet Functions | |||
Calculate A Date Excluding Weekends And Holidays | New Users to Excel | |||
How to Calculate Dates without counting the weekends | Excel Worksheet Functions |