Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coding to show first _weekday_ prior to a date, when date calculation happens to fall on weekend?
I created a spreadsheet "date calculator" that has helped me
enormously in work. I'm with a legal firm's marketing dept and we receive countless notices of events taking place at a local large performance centre. My job is to send out an email each time to all the lawyers so that they know what they can take clients to and then alert my boss 3 weeks prior and then 1 week prior to get her feedback on what action to take for each event based on the tickets requested (or tickets not requested, as the case may be). The problem with this is when the date returned falls on the weekend. I need for the spreadsheet to return a weekday in all cases. So if the alert happens to fall on a weekday, fine. But if it falls on a weekend, the spreadsheet would need to offset that date to show the Friday just previous to that weekend. For example, there's an event on Sat Jan 29 2005 - the spreadsheet returns Sat Jan 8th as 3 weeks before and Sat Jan 22 as 1 week before as when I must update my boss. Any way to make the calculation return the Friday before in this type of case?? The calculation is currently an easy one, I have the cell format to show as a custom date of: ddd., mmm.dd.yyyy And the 3 week code is: =IF(B3<"",SUM(B3-21),"") And the 1 week code is: =IF(B3<"",SUM(B3-7),"") Thanks much for any help! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coding to show first _weekday_ prior to a date, when date calculation happens to fall on weekend?
Pete,
use =IF(B3<"",B3-21-(WEEKDAY(B3-21)=1)*2-(WEEKDAY(B3-21)=7)*1,"") and =IF(B3<"",B3-7-(WEEKDAY(B3-7)=1)*2-(WEEKDAY(B3-7)=7)*1,"") -- HTH ------- Bob Phillips "StargateFan" wrote in message ... I created a spreadsheet "date calculator" that has helped me enormously in work. I'm with a legal firm's marketing dept and we receive countless notices of events taking place at a local large performance centre. My job is to send out an email each time to all the lawyers so that they know what they can take clients to and then alert my boss 3 weeks prior and then 1 week prior to get her feedback on what action to take for each event based on the tickets requested (or tickets not requested, as the case may be). The problem with this is when the date returned falls on the weekend. I need for the spreadsheet to return a weekday in all cases. So if the alert happens to fall on a weekday, fine. But if it falls on a weekend, the spreadsheet would need to offset that date to show the Friday just previous to that weekend. For example, there's an event on Sat Jan 29 2005 - the spreadsheet returns Sat Jan 8th as 3 weeks before and Sat Jan 22 as 1 week before as when I must update my boss. Any way to make the calculation return the Friday before in this type of case?? The calculation is currently an easy one, I have the cell format to show as a custom date of: ddd., mmm.dd.yyyy And the 3 week code is: =IF(B3<"",SUM(B3-21),"") And the 1 week code is: =IF(B3<"",SUM(B3-7),"") Thanks much for any help! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coding to show first _weekday_ prior to a date, when date calculation happens to fall on weekend?
another way
=IF(B3<"",B3-21-CHOOSE(WEEKDAY(B3-21),2,0,0,0,0,0,1),"") -- HTH ------- Bob Phillips "Bob Phillips" wrote in message ... Pete, use =IF(B3<"",B3-21-(WEEKDAY(B3-21)=1)*2-(WEEKDAY(B3-21)=7)*1,"") and =IF(B3<"",B3-7-(WEEKDAY(B3-7)=1)*2-(WEEKDAY(B3-7)=7)*1,"") -- HTH ------- Bob Phillips "StargateFan" wrote in message ... I created a spreadsheet "date calculator" that has helped me enormously in work. I'm with a legal firm's marketing dept and we receive countless notices of events taking place at a local large performance centre. My job is to send out an email each time to all the lawyers so that they know what they can take clients to and then alert my boss 3 weeks prior and then 1 week prior to get her feedback on what action to take for each event based on the tickets requested (or tickets not requested, as the case may be). The problem with this is when the date returned falls on the weekend. I need for the spreadsheet to return a weekday in all cases. So if the alert happens to fall on a weekday, fine. But if it falls on a weekend, the spreadsheet would need to offset that date to show the Friday just previous to that weekend. For example, there's an event on Sat Jan 29 2005 - the spreadsheet returns Sat Jan 8th as 3 weeks before and Sat Jan 22 as 1 week before as when I must update my boss. Any way to make the calculation return the Friday before in this type of case?? The calculation is currently an easy one, I have the cell format to show as a custom date of: ddd., mmm.dd.yyyy And the 3 week code is: =IF(B3<"",SUM(B3-21),"") And the 1 week code is: =IF(B3<"",SUM(B3-7),"") Thanks much for any help! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coding to show first _weekday_ prior to a date, when date calculation happens to fall on weekend?
On Wed, 08 Dec 2004 07:43:31 -0500, StargateFan
wrote: And the 3 week code is: =IF(B3<"",SUM(B3-21),"") =IF(B3<"",workday(B3-20,-1),"") And the 1 week code is: =IF(B3<"",SUM(B3-7),"") =IF(B3<"",workday(B3-6,-1),"") If the WORKDAY function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in. On the Tools menu, click Add-Ins. In the Add-Ins available list, select the Analysis ToolPak box, and then click OK. If necessary, follow the instructions in the setup program. --ron |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coding to show first _weekday_ prior to a date, when date calculation happens to fall on weekend?
On Wed, 8 Dec 2004 13:15:07 -0000, "Bob Phillips"
wrote: Pete, use =IF(B3<"",B3-21-(WEEKDAY(B3-21)=1)*2-(WEEKDAY(B3-21)=7)*1,"") and =IF(B3<"",B3-7-(WEEKDAY(B3-7)=1)*2-(WEEKDAY(B3-7)=7)*1,"") -- HTH It sure did. It works here on my home version like a charm. I found something odd, though. At work, I have to key in dd/mm/yyyy for the calculation to work (day first). When I brought the spreadsheet home it wouldn't work until I keyed in mm/dd/yyyy instead (month first). I'm wondering why the difference? Is this a regional setting thing, or an Excel setting thing somewhere, anyone know? Once I know how to do that, I'll be able to watch out for this in future. I've never run into this type of thing before. All my spreadsheets have worked wherever I have gone. Since I don't modify anything to do with this type of thing during my install of Excel, I'm thinking this might be controlled somewhere else. My last day is Friday, so I'll be able to leave _all_ the pending events for the next 6 months that I've already received printed up and in their respective BF (bring forward) folder so that the new person will know exactly when to do all the email notifications for all of these events. There were so many and it otherwise would have taken me a long time to do manually. Thanks so much, everyone! You're all great to give so much great help. This will be a phenomenal sheet once I fix the one at work! :oD ------- Bob Phillips "StargateFan" wrote in message .. . I created a spreadsheet "date calculator" that has helped me enormously in work. I'm with a legal firm's marketing dept and we receive countless notices of events taking place at a local large performance centre. My job is to send out an email each time to all the lawyers so that they know what they can take clients to and then alert my boss 3 weeks prior and then 1 week prior to get her feedback on what action to take for each event based on the tickets requested (or tickets not requested, as the case may be). The problem with this is when the date returned falls on the weekend. I need for the spreadsheet to return a weekday in all cases. So if the alert happens to fall on a weekday, fine. But if it falls on a weekend, the spreadsheet would need to offset that date to show the Friday just previous to that weekend. For example, there's an event on Sat Jan 29 2005 - the spreadsheet returns Sat Jan 8th as 3 weeks before and Sat Jan 22 as 1 week before as when I must update my boss. Any way to make the calculation return the Friday before in this type of case?? The calculation is currently an easy one, I have the cell format to show as a custom date of: ddd., mmm.dd.yyyy And the 3 week code is: =IF(B3<"",SUM(B3-21),"") And the 1 week code is: =IF(B3<"",SUM(B3-7),"") Thanks much for any help! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coding to show first _weekday_ prior to a date, when date calculation happens to fall on weekend?
On Wed, 08 Dec 2004 17:40:20 -0500, Ron Rosenfeld
wrote: On Wed, 08 Dec 2004 07:43:31 -0500, StargateFan wrote: And the 3 week code is: =IF(B3<"",SUM(B3-21),"") =IF(B3<"",workday(B3-20,-1),"") And the 1 week code is: =IF(B3<"",SUM(B3-7),"") =IF(B3<"",workday(B3-6,-1),"") If the WORKDAY function is not available, and returns the #NAME? error, install I did get that error. <g and load the Analysis ToolPak add-in. Thanks, I'll look for it. I wouldn't be able to do that at work, as you can imagine, as I can't install anything but it'll be a good add-in to have here at home. On the Tools menu, click Add-Ins. In the Add-Ins available list, select the Analysis ToolPak box, and then click OK. If necessary, follow the instructions in the setup program. --ron Thank you! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Show prior year based on date in other cell | Excel Worksheet Functions | |||
formual to determine if date falls on weekend, adjust date to Mond | Excel Discussion (Misc queries) | |||
Formula for determining if two date columns fall within specific date range | Excel Worksheet Functions | |||
Formula for determining if two date columns fall within specific date range | Excel Discussion (Misc queries) | |||
Validate date within range prior to calculation | Excel Discussion (Misc queries) |