Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate Date
I am trying to create a formula that will take a specific date in a cell and
give the date 35 days prior. I am only concerned with Mon-Fri. So, if the date falls on a Saturday then give the Friday date. If the date falls on a Sunday, then give the Monday date. I am not sure where to begin. I wrote the following function of "= A2 - 32" Any help is greatly appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate Date
Hi
if you have the analysis toolpak installed (tools / addins / analysis toolpak) you can use the WORKDAY function e.g. =WORKDAY(A2,-35) Cheers JulieD "Jerome" wrote in message ... I am trying to create a formula that will take a specific date in a cell and give the date 35 days prior. I am only concerned with Mon-Fri. So, if the date falls on a Saturday then give the Friday date. If the date falls on a Sunday, then give the Monday date. I am not sure where to begin. I wrote the following function of "= A2 - 32" Any help is greatly appreciated. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate Date
Thanks JulieD,
When filling down the formula I receive "#VALUE!" or "#NUM!" on cells without irrelavant data. How do I account for this? I tried writing the formula as =WORKDAY (B6,-35, " ") with no success. "JulieD" wrote: Hi if you have the analysis toolpak installed (tools / addins / analysis toolpak) you can use the WORKDAY function e.g. =WORKDAY(A2,-35) Cheers JulieD "Jerome" wrote in message ... I am trying to create a formula that will take a specific date in a cell and give the date 35 days prior. I am only concerned with Mon-Fri. So, if the date falls on a Saturday then give the Friday date. If the date falls on a Sunday, then give the Monday date. I am not sure where to begin. I wrote the following function of "= A2 - 32" Any help is greatly appreciated. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate Date
Pardon me - irrelavant should be relavant. Cells without relavant data.
"Jerome" wrote: Thanks JulieD, When filling down the formula I receive "#VALUE!" or "#NUM!" on cells without irrelavant data. How do I account for this? I tried writing the formula as =WORKDAY (B6,-35, " ") with no success. "JulieD" wrote: Hi if you have the analysis toolpak installed (tools / addins / analysis toolpak) you can use the WORKDAY function e.g. =WORKDAY(A2,-35) Cheers JulieD "Jerome" wrote in message ... I am trying to create a formula that will take a specific date in a cell and give the date 35 days prior. I am only concerned with Mon-Fri. So, if the date falls on a Saturday then give the Friday date. If the date falls on a Sunday, then give the Monday date. I am not sure where to begin. I wrote the following function of "= A2 - 32" Any help is greatly appreciated. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate Date
On Thu, 3 Mar 2005 08:43:07 -0800, Jerome
wrote: I am trying to create a formula that will take a specific date in a cell and give the date 35 days prior. I am only concerned with Mon-Fri. So, if the date falls on a Saturday then give the Friday date. If the date falls on a Sunday, then give the Monday date. I am not sure where to begin. I wrote the following function of "= A2 - 32" Any help is greatly appreciated. If I understand you correctly, you first want to subtract 35 days from the particular date, and then change it only if that date falls on a weekend. The function "=A2-32" will give you a date 32 days prior, not 35. For a day 35 days prior, altered as you instruct if the day falls on a weekend, use: =A2-35+CHOOSE(WEEKDAY(A2-35),1,0,0,0,0,0,-1) If, on the other hand, you are looking for 35 working days prior to the date in A2, so that no weekends are even counted, then look at the WORKDAY function. --ron |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate Date
I want to be able to copy the formula (fill down) to the end of the column.
There are spaces and column headers in between valid data. How do I prevent the "#VALUE!" and "#NUM!" from showing up? "Ron Rosenfeld" wrote: On Thu, 3 Mar 2005 08:43:07 -0800, Jerome wrote: I am trying to create a formula that will take a specific date in a cell and give the date 35 days prior. I am only concerned with Mon-Fri. So, if the date falls on a Saturday then give the Friday date. If the date falls on a Sunday, then give the Monday date. I am not sure where to begin. I wrote the following function of "= A2 - 32" Any help is greatly appreciated. If I understand you correctly, you first want to subtract 35 days from the particular date, and then change it only if that date falls on a weekend. The function "=A2-32" will give you a date 32 days prior, not 35. For a day 35 days prior, altered as you instruct if the day falls on a weekend, use: =A2-35+CHOOSE(WEEKDAY(A2-35),1,0,0,0,0,0,-1) If, on the other hand, you are looking for 35 working days prior to the date in A2, so that no weekends are even counted, then look at the WORKDAY function. --ron |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate Date
Hi Jerome
nest it if an IF statement e.g. =IF(B6<"",WORKDAY(B6,-35),"") or whatever test you want to use Cheers JulieD "Jerome" wrote in message ... Pardon me - irrelavant should be relavant. Cells without relavant data. "Jerome" wrote: Thanks JulieD, When filling down the formula I receive "#VALUE!" or "#NUM!" on cells without irrelavant data. How do I account for this? I tried writing the formula as =WORKDAY (B6,-35, " ") with no success. "JulieD" wrote: Hi if you have the analysis toolpak installed (tools / addins / analysis toolpak) you can use the WORKDAY function e.g. =WORKDAY(A2,-35) Cheers JulieD "Jerome" wrote in message ... I am trying to create a formula that will take a specific date in a cell and give the date 35 days prior. I am only concerned with Mon-Fri. So, if the date falls on a Saturday then give the Friday date. If the date falls on a Sunday, then give the Monday date. I am not sure where to begin. I wrote the following function of "= A2 - 32" Any help is greatly appreciated. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate Date
On Thu, 3 Mar 2005 13:55:03 -0800, Jerome
wrote: I want to be able to copy the formula (fill down) to the end of the column. There are spaces and column headers in between valid data. How do I prevent the "#VALUE!" and "#NUM!" from showing up? Two methods, assuming the data starts in A2 and the formula is in B2: 1. Embed your formula in an IF statement: =IF(A2="","",A2-35+CHOOSE(WEEKDAY(A2-35),1,0,0,0,0,0,-1)) 2. Use conditional formatting to format the cell with a font the same color as the background color (e.g. white) if there is an error: Format/Conditional Formatting Formula Is: =ISERROR(B2) --ron |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate Date
Thank you Julie that worked.
"JulieD" wrote: Hi Jerome nest it if an IF statement e.g. =IF(B6<"",WORKDAY(B6,-35),"") or whatever test you want to use Cheers JulieD "Jerome" wrote in message ... Pardon me - irrelavant should be relavant. Cells without relavant data. "Jerome" wrote: Thanks JulieD, When filling down the formula I receive "#VALUE!" or "#NUM!" on cells without irrelavant data. How do I account for this? I tried writing the formula as =WORKDAY (B6,-35, " ") with no success. "JulieD" wrote: Hi if you have the analysis toolpak installed (tools / addins / analysis toolpak) you can use the WORKDAY function e.g. =WORKDAY(A2,-35) Cheers JulieD "Jerome" wrote in message ... I am trying to create a formula that will take a specific date in a cell and give the date 35 days prior. I am only concerned with Mon-Fri. So, if the date falls on a Saturday then give the Friday date. If the date falls on a Sunday, then give the Monday date. I am not sure where to begin. I wrote the following function of "= A2 - 32" Any help is greatly appreciated. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate Date
Thank you Ron, this method works also.
"Ron Rosenfeld" wrote: On Thu, 3 Mar 2005 13:55:03 -0800, Jerome wrote: I want to be able to copy the formula (fill down) to the end of the column. There are spaces and column headers in between valid data. How do I prevent the "#VALUE!" and "#NUM!" from showing up? Two methods, assuming the data starts in A2 and the formula is in B2: 1. Embed your formula in an IF statement: =IF(A2="","",A2-35+CHOOSE(WEEKDAY(A2-35),1,0,0,0,0,0,-1)) 2. Use conditional formatting to format the cell with a font the same color as the background color (e.g. white) if there is an error: Format/Conditional Formatting Formula Is: =ISERROR(B2) --ron |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate Date
you're welcome and thanks for the feedback
"Jerome" wrote in message ... Thank you Julie that worked. "JulieD" wrote: Hi Jerome nest it if an IF statement e.g. =IF(B6<"",WORKDAY(B6,-35),"") or whatever test you want to use Cheers JulieD "Jerome" wrote in message ... Pardon me - irrelavant should be relavant. Cells without relavant data. "Jerome" wrote: Thanks JulieD, When filling down the formula I receive "#VALUE!" or "#NUM!" on cells without irrelavant data. How do I account for this? I tried writing the formula as =WORKDAY (B6,-35, " ") with no success. "JulieD" wrote: Hi if you have the analysis toolpak installed (tools / addins / analysis toolpak) you can use the WORKDAY function e.g. =WORKDAY(A2,-35) Cheers JulieD "Jerome" wrote in message ... I am trying to create a formula that will take a specific date in a cell and give the date 35 days prior. I am only concerned with Mon-Fri. So, if the date falls on a Saturday then give the Friday date. If the date falls on a Sunday, then give the Monday date. I am not sure where to begin. I wrote the following function of "= A2 - 32" Any help is greatly appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calculate a date, 10 weeks after a set date & return a specific da | Excel Worksheet Functions | |||
Trying to calculate end date using order date and lead time variab | Excel Worksheet Functions | |||
Auto calculate for date + days forward to yield new date | Excel Worksheet Functions | |||
formula to calculate age using birth date and current date | Excel Worksheet Functions | |||
formula to calculate future date from date in cell plus days | Excel Worksheet Functions |