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?
I assume your retention on the monthly tapes is "3 years" rather than 1,095 days? 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. 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 --brian -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Kurt Buff Sent: Wednesday, September 9, 2015 4:53 PM To: ntsysadm <[email protected]> Subject: [NTSysADM] OT-ish: date calculation in Excel I'm taking over backup duties for the company. We're sending weekly tapes to be returned in a month, monthly tapes to be returned in 3 years, and we don't yet have a target return date for yearly tapes. The previous guy has put together an Excel spreadsheet for the tape rotation offsite, and date calculation is completely missing from it - you have to page through the calendar to figure out when you want the tapes to be returned to the office. That's inconvenient at best. For the weekly tapes, I'd like to set up a formula so that I can enter the ship date, and the formula will calculate which Wednesday of the month the ship date is (1st,2nd, etc), and populate the return date field with matching DoW 4 or 5 weeks later (depending on how many Wednesdays there are in the shipping month - and if the shipping date is the 5th Wednesday of the month, return on either the 4th Wednesday of the next month, or the 1st Wednesday of the month after that - I don't care which, really). For the return date on the monthly tapes, I'm satisfied with using '=edate(cell#,36)' and be done with it, since precision of return date after 3 years isn't a very high priority, though it would be kinda cool to specify an actual Wednesday for return. Some day they'll figure out how long they want to keep the yearly tapes - but I'm not holding my breath... I've been googleing for a while, and trying different things, and I can't seem to make it work. Anyone have a nifty formula for this? For those who care, we're using DPM. I've thought about ginning something up in powershell - if nobody has a nice Excel formula, I will probably pursue that angle, but I'd prefer something in Excel, to keep things a bit simpler. Kurt
