Go Back   Access World Forums > Apps and Windows > Excel

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-29-2018, 10:24 AM   #1
raziel3
Newly Registered User
 
Join Date: Oct 2017
Posts: 41
Thanks: 15
Thanked 0 Times in 0 Posts
raziel3 is on a distinguished road
Get same day of previous month

How to I get the same day, not the same date, of the previous month from a date. For example the 29/11/2018 is the last Thursday of November, how can i get the last Thursday for October, 2018 or the 1st Friday or the 2nd Tuesday?

raziel3 is offline   Reply With Quote
Old 11-29-2018, 10:35 AM   #2
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 7,437
Thanks: 92
Thanked 1,831 Times in 1,701 Posts
isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light
Re: Get same day of previous month

Here's one way
Deduct 28 days. Check if month has changed. If not deduct 7 more days

OR a different way of stating the same thing
If date is <=28, deduct 28 days, otherwise deduct 35
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders : Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 11-29-2018, 10:46 AM   #3
raziel3
Newly Registered User
 
Join Date: Oct 2017
Posts: 41
Thanks: 15
Thanked 0 Times in 0 Posts
raziel3 is on a distinguished road
Re: Get same day of previous month

Quote:
Originally Posted by isladogs View Post
Here's one way
Deduct 28 days. Check if month has changed. If not deduct 7 more days

OR a different way of stating the same thing
If date is <=28, deduct 28 days, otherwise deduct 35
So something like this? A1 has the date to compare.

IF(MONTH(A1)=MONTH(A1),A1-28,A1-35)

raziel3 is offline   Reply With Quote
Old 11-29-2018, 11:36 AM   #4
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 7,437
Thanks: 92
Thanked 1,831 Times in 1,701 Posts
isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light
Re: Get same day of previous month

Not quite that. Try
Code:
=IF(MONTH(A1-28)=MONTH(A1),A1-35,A1-28)
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders : Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
raziel3 (11-30-2018)
Old 11-29-2018, 02:57 PM   #5
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,329
Thanks: 80
Thanked 1,418 Times in 1,338 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Get same day of previous month

Quote:
Originally Posted by raziel3 View Post
How to I get the same day, not the same date, of the previous month from a date. For example the 29/11/2018 is the last Thursday of November, how can i get the last Thursday for October, 2018 or the 1st Friday or the 2nd Tuesday?
Could there be some ambiguity in this specification?

For example, the last Friday could be either the fourth or fifth Friday in the month.
Galaxiom is offline   Reply With Quote
Old 11-29-2018, 03:06 PM   #6
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 7,437
Thanks: 92
Thanked 1,831 Times in 1,701 Posts
isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light
Re: Get same day of previous month

Quote:
Originally Posted by Galaxiom View Post
Could there be some ambiguity in this specification?

For example, the last Friday could be either the fourth or fifth Friday in the month.
Whilst that's true and the last Friday of this month 30/11/2018 is indeed the fifth Friday this month, it's still the case that the last Friday in October was 26/10 even though its the 4th Friday.

Now if we were to reference the 4th Friday in Nov (23/11), the corresponding date in Oct is still 26/10.

Of course, the point you are making is that if we reference the 5th Friday in Nov, there is no equivalent date in Oct
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders : Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 11-29-2018, 06:31 PM   #7
raziel3
Newly Registered User
 
Join Date: Oct 2017
Posts: 41
Thanks: 15
Thanked 0 Times in 0 Posts
raziel3 is on a distinguished road
Re: Get same day of previous month

Quote:
Originally Posted by Galaxiom View Post
Could there be some ambiguity in this specification?

For example, the last Friday could be either the fourth or fifth Friday in the month.
There's more ambiguity when comparing dates rather than a day of the month. For example if I want to compare sales for the 24/11/18 against the previous month then that would not be accurate because the 24/11/18 was a Saturday whereas 24/10/18 was a Wednesday. People tend to buy more on weekends rather than weekdays so for thoroughness I want to compare the relative day of each month.


Last edited by raziel3; 11-29-2018 at 06:41 PM.
raziel3 is offline   Reply With Quote
Old 11-29-2018, 10:08 PM   #8
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 7,437
Thanks: 92
Thanked 1,831 Times in 1,701 Posts
isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light
Re: Get same day of previous month

Raziel
Did you see my solution in post 4
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders : Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 11-30-2018, 05:29 AM   #9
raziel3
Newly Registered User
 
Join Date: Oct 2017
Posts: 41
Thanks: 15
Thanked 0 Times in 0 Posts
raziel3 is on a distinguished road
Re: Get same day of previous month

Yes I did, thank you. I was just replying to Galaxiom.

raziel3 is offline   Reply With Quote
Reply

Tags
excel 2013 , excel 2016

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Query to pull out 2 month, 6 month and 12 month extracts based on a previous query Cark Queries 7 05-24-2017 06:29 AM
Getting previous month name Agom008 Tables 7 09-04-2014 02:43 PM
Sbracting previous month data from current month jband Queries 17 05-15-2013 11:21 AM
carrying a previous month's balance to current month icemonster General 2 03-16-2010 04:34 AM
Previous month Rockape Queries 6 08-06-2008 12:16 AM




All times are GMT -8. The time now is 11:02 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World