For your Wednesdays - Weekday() and check for 4 Or weekday(date,14) and check for 1?
And to get from whatever day of the week you are on - 7-weekday(date,14) Or maybe use 15 depending on what you want if the current date is a Wednesday - Current day Noon shipping ain't easy if it's already 13:00 For your month start dates there is (date-day(date)+1) Or date(yyyy,mm,1) Calculation for the Wednesday number in the month - Calculate the Wednesday date, then maybe get it as day of month, then add 6 and take int(day(TheWednesday)+6) Remembering that for the 4th Wed from Feb 3rd that will actually be the 1st wed in March! Me - I'd just use a 5 week cycle so there's always storage sets going both ways Re 1,095 ... 1095/7 = 156 and a bit - so that won't be a Wednesday return if the ship out was a Wednesday Maybe 157 weeks would be a better return calculation. BUT What about Xmas? - New Year and other Public Holidays that can fall on a Wednesday? Going to use the next working day, or the next Wednesday that isn't also a holiday? JimB -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Melvin Backus Sent: Thursday, September 10, 2015 2:10 PM To: [email protected] Subject: RE: [NTSysADM] OT-ish: date calculation in Excel I read an article a couple of years ago which predicted that medical technology would be able to halt the effects of aging within 25 years, and would be able to reverse the effects within 40 years. I'm personally counting on them being right. Hey, if I've got to work, IT isn't the worst job I can think of. :) -- There are 10 kinds of people in the world... those who understand binary and those who don't. -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Maglinger, Paul Sent: Thursday, September 10, 2015 9:06 AM To: [email protected] Subject: RE: [NTSysADM] OT-ish: date calculation in Excel I'm not sure about everyone else on this list, but I don't plan on being in IT quite that long. -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Freddy Grande Sent: Thursday, September 10, 2015 1:56 AM To: [email protected] Subject: RE: [NTSysADM] OT-ish: date calculation in Excel If you factor in leap years don't forget that the common simplistic rule of "every year divisible by 4 is a leap year" is wrong https://en.wikipedia.org/wiki/Leap_year#Algorithm Not that it matters until 2100 :P Regards, Freddy -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Kurt Buff Sent: Thursday, 10 September 2015 1:38 PM To: ntsysadm <[email protected]> Subject: Re: [NTSysADM] OT-ish: date calculation in Excel On Wed, Sep 9, 2015 at 6:46 PM, Brian Desmond <[email protected]> wrote: > Kurt- > > Is there a reason you don't always want the tapes retained for four > weeks? Is it that your retention is defined as "a month" rather than say 28 > days? First - thanks for this. I appreciate it. But that's basically it - it's stated as 3 months. I could have just put it at 28 days (or 31 days, and corrected for the length of the month), but that still doesn't correct for the Wednesday delivery. > I assume your retention on the monthly tapes is "3 years" rather than 1,095 > days? Yep. > Anyway, I think I got what you laid out. I borrowed the formula from > here > http://www.mrexcel.com/forum/excel-questions/523063-number-weeks-month > .html for part of it. There's probably an easier way to do it but it > was an interesting problem for my short flight this afternoon. I didn't > figure out how to factor in leap years so your annual retention is > potentially off by a couple days. I can certainly live with that. > If you simplify to retention in days rather than months it's way > easier. I posted it here if anyone wants to look. > http://1drv.ms/1KFouIY Actually, I'm going to raise the possibility to management of putting it to being 42 days, rather than a month, or 28 days. I think that covers long months and holidays better. Until then, I'll work with your example. Unfortunately, the link you sent says it's empty, and there's no attachment to your email. Kurt
