Solved

25th of every month

Posted on 2016-10-04
7
50 Views
Last Modified: 2016-10-06
Experts,

How can I show the 25th of the month based on the current date and if the 25 of the month has passed then use the next month?  

ie:
today = Oct 5
result:  Oct 25

if today = Oct 26 then
result = Nov 25th

==>but if it falls on a Friday or Saturday then use the preceding.

thank you

example
0
Comment
Question by:pdvsa
7 Comments
 
LVL 49

Expert Comment

by:Ryan Chong
Comment Utility
try this:

=IF(DAY(TODAY()) <= 25, TEXT(TODAY(), "MMM DD"), TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())+1, 25), "MMM DD"))

Open in new window

0
 
LVL 49

Expert Comment

by:Ryan Chong
Comment Utility
you can also use this if you want to return a date value instead.

=IF(DAY(TODAY()) <= 25, DATE(YEAR(TODAY()),MONTH(TODAY()), 25), DATE(YEAR(TODAY()),MONTH(TODAY())+1, 25))

Open in new window


use the Number Format to format the date value accordingly.
0
 
LVL 48

Expert Comment

by:Rgonzo1971
Comment Utility
Hi,

pls try

=EOMONTH(DATE(YEAR(A1),MONTH(A1),DAY(A1)-25),0)+25-WEEKDAY(EOMONTH(DATE(YEAR(A1),MONTH(A1),DAY(A1)-25),0)+25)+MIN(5,WEEKDAY(EOMONTH(DATE(YEAR(A1),MONTH(A1),DAY(A1)-25),0)+25))

Open in new window

Regards
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 8

Expert Comment

by:Koen
Comment Utility
try this :

=WORKDAY(IF(DAY(TODAY())<=25;DATE(YEAR(TODAY());MONTH(TODAY());25);DATE(IF(MONTH(TODAY())<12;YEAR(TODAY());YEAR(TODAY())+1);IF(MONTH(TODAY())<12;MONTH(TODAY())+1;1);25));0)

(you might need to replace ; with , depending on your regional settings)
0
 
LVL 48

Accepted Solution

by:
Rgonzo1971 earned 250 total points
Comment Utility
Hi,

pls try (shorter)
=WORKDAY.INTL(EOMONTH(DATE(YEAR(A1),MONTH(A1),DAY(A1)-25),0)+26,-1,7)

Open in new window

Regards
2
 
LVL 31

Assisted Solution

by:Rob Henson
Rob Henson earned 250 total points
Comment Utility
RGonzo - excellent solution as always!!

Stating the obvious, so no points for this, - to convert to using today rather than fixed cell:

=WORKDAY.INTL(EOMONTH(DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())-25),0)+26,-1,7)

Also assumes, Excel 2010 or later. A previous question from pdvsa quoted Excel 2016 so I don't think that will be a problem.
0
 

Author Closing Comment

by:pdvsa
Comment Utility
Nice!  Sorry  for my tardy reply. I got a little sidetracked with my job. I don't like this job anymore
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now