Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-13-2014, 07:49 PM
macropod's Avatar
macropod macropod is offline 'Monday to Friday' Date calculation help needed Windows 7 64bit 'Monday to Friday' Date calculation help needed Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 22,467
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

The get a Monday start date for the 'dates of availability between', change the 'jd':
{SET D8jd{=jd+84}}
in the third field in D8 to 'INT(jd/7)*7', thus:
{SET D8jd{=INT(jd/7)*7+84}}

As for what the field codes are doing, in B2, the:
{ASK StartDate "Issue Date"}
simply asks for the "Issue Date". The:


{SET a{=INT((14-{StartDate \@ M})/12)}}
{SET b{={StartDate \@ yyyy}+4800-a}}
{SET c{={StartDate \@ M}+12*a-3}}
{SET d{StartDate \@ d}}
{SET jd{=d+INT((153*c+2)/5)+365*b+INT(b/4)-INT(b/100)+INT(b/400)-32045}}
calculates the Julian day number for the "Issue Date". The Julian day number is then used as the starting point for calculating all the other dates.

The:
{SET Tgtjd{=jd+126}}
{SET Tgte{=INT((4*(Tgtjd+32044)+3)/146097)}}
{SET Tgtf{=Tgtjd+32044-INT(146097*Tgte/4)}}
{SET Tgtg{=INT((4*Tgtf+3)/1461)}}
{SET Tgth{=Tgtf-INT(1461*Tgtg/4)}}
{SET Tgti{=INT((5*Tgth+2)/153)}}
{SET Tgtd{=Tgth-INT((153*Tgti+2)/5)+1}}
{SET Tgtm{=Tgti+3-12*INT(Tgti/10)}}
{SET Tgty{=100*Tgte+Tgtg-4800+INT(Tgti/10)}}
calculates the default values for the "Target Date" day, month and year, which the next field displays as the default when activated:
{ASK TargetDate "Target Date" \d "{Tgtd \# 00}/{Tgtm \# 00}/{Tgty}"}
In fact, if you remove the 'Tgt' prefix of these codes and the 'B2' etc. prefixes (which are simply the paragraph #s) from all the others, you'll see they're all essentially the same set of field codes. The reason for the prefixes is to ensure the bookmark values each SET field creates are unique to its own series of calculations. The # days to add to the original Julian day number is determined by the fields coded like:
{SET B2jd {=jd+14}}
For the fields where a Monday is needed, all that requires is to calculate the nearest preceding Julian day number that's exactly divisible by 7, which is obtained via INT(jd/7)*7. For Fridays, simply take that result and add 4.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply



Similar Threads
Thread Thread Starter Forum Replies Last Post
'Monday to Friday' Date calculation help needed Age calculation with date and DOB fields Xaand Word 9 09-29-2023 11:38 AM
'Monday to Friday' Date calculation help needed Rules based due date calculation dlowrey Excel Programming 3 05-12-2013 08:30 PM
'Monday to Friday' Date calculation help needed Date Calculation Lights Excel 5 04-18-2012 04:31 AM
calculation of start date ketanco Project 1 02-29-2012 07:01 AM
Help needed using the serial number date with sumifs - whole office is stumped FraserKitchell Excel 3 01-06-2010 12:24 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 06:59 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft