Get same day of previous month (1 Viewer)

raziel3

Registered User.
Local time
Today, 13:54
Joined
Oct 5, 2017
Messages
273
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?
 

isladogs

MVP / VIP
Local time
Today, 17:54
Joined
Jan 14, 2017
Messages
18,186
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
 

raziel3

Registered User.
Local time
Today, 13:54
Joined
Oct 5, 2017
Messages
273
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)
 

isladogs

MVP / VIP
Local time
Today, 17:54
Joined
Jan 14, 2017
Messages
18,186
Not quite that. Try
Code:
=IF(MONTH(A1-28)=MONTH(A1),A1-35,A1-28)
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 04:54
Joined
Jan 20, 2009
Messages
12,849
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.
 

isladogs

MVP / VIP
Local time
Today, 17:54
Joined
Jan 14, 2017
Messages
18,186
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
 

raziel3

Registered User.
Local time
Today, 13:54
Joined
Oct 5, 2017
Messages
273
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:

isladogs

MVP / VIP
Local time
Today, 17:54
Joined
Jan 14, 2017
Messages
18,186
Raziel
Did you see my solution in post 4
 

raziel3

Registered User.
Local time
Today, 13:54
Joined
Oct 5, 2017
Messages
273
Yes I did, thank you. I was just replying to Galaxiom.
 

Users who are viewing this thread

Top Bottom