UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Increment Date By One Month, Office 2013    
 
   
billwild
post Feb 26 2020, 08:58 AM
Post#1



Posts: 126
Joined: 4-June 02



I want a formula for calculating the Xth Weekday of ANY month. For Example Column A would be 2/26/20, the 4th Wednesday of February, Column B would be 3/25,20, Column C would be 4/22/20


This post has been edited by billwild: Feb 26 2020, 09:02 AM

--------------------
always learning....<img src="/forums/images/graemlins/laugh.gif" alt="" />
Go to the top of the page
 
DanielPineault
post Feb 26 2020, 09:00 AM
Post#2


UtterAccess VIP
Posts: 7,237
Joined: 30-June 11



You can use DateAdd() for such things.

--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
billwild
post Feb 26 2020, 09:27 AM
Post#3



Posts: 126
Joined: 4-June 02



I don't think the DateAdd() is available in the last version of Excel.

--------------------
always learning....<img src="/forums/images/graemlins/laugh.gif" alt="" />
Go to the top of the page
 
DanielPineault
post Feb 26 2020, 09:43 AM
Post#4


UtterAccess VIP
Posts: 7,237
Joined: 30-June 11



It's available through VBA

You can create your own wrapper function and then call it in your worksheet

Public Function myDateAdd(MyInterval As String, MyNumber As Double, MyDate As Variant) As Variant
myDateAdd = DateAdd(MyInterval, MyNumber, MyDate)
End Function

=myDateAdd("m",2,"2020-02-26")

--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
billwild
post Feb 26 2020, 12:20 PM
Post#5



Posts: 126
Joined: 4-June 02



This does work for what I need.
For Example Column A would start our being being 2/26/20, then the 4th Wednesday of March, (Column B) would be 3/25,20, Column C would be 4/22/20

In your example the function results with 4/26/20 which is 2 months from 2/26/20). I need it to be the 4th week of March (3/25/20, then the 4th week of April (4/22/20), etc.

--------------------
always learning....<img src="/forums/images/graemlins/laugh.gif" alt="" />
Go to the top of the page
 
cheekybuddha
post Feb 26 2020, 12:23 PM
Post#6


UtterAccess Moderator
Posts: 12,601
Joined: 6-December 03
From: Telegraph Hill


How about:
CODE
=myDateAdd("d",28,"2020-02-26")

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Feb 26 2020, 12:25 PM
Post#7


UtterAccess Moderator
Posts: 12,601
Joined: 6-December 03
From: Telegraph Hill


You can also probably bypass the VBA and do it directly in a cell formula:
CODE
=A1 + 28


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
billwild
post Feb 26 2020, 12:59 PM
Post#8



Posts: 126
Joined: 4-June 02



That's not gonna work either because 28 days is NOT the 4th Wed of a month. The calculation must be the 4th Wednesday of the month in the proceeding cell..
This post has been edited by billwild: Feb 26 2020, 01:02 PM

--------------------
always learning....<img src="/forums/images/graemlins/laugh.gif" alt="" />
Go to the top of the page
 
cheekybuddha
post Feb 26 2020, 01:27 PM
Post#9


UtterAccess Moderator
Posts: 12,601
Joined: 6-December 03
From: Telegraph Hill


So, if A1 = 2020-02-26, then your formula should also show 202-02-26 ?

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    28th March 2020 - 10:15 AM