Subtracting one month from a date but making sure it is the first business day of that month

how to calculate working days in excel excluding weekends and holidays
excel first working day of month
first day of month excel
workday excel
first day of next month excel
first day of each month
subtract business days in excel
excel holiday function

I need to subtract one month from a date called ArchiveDate but I want the first business day of that month. For example if my ArchiveDate is 9/2/2018 I would like to have 8/1/2019.

This is what I have:

DECLARE @ArchiveDate date = '9/2/2019'

SELECT ArchiveDate = DATEADD(day, 
        CASE WHEN 
            DATEPART(weekday, DATEADD(MONTH, -1, @ArchiveDate)) = 1
            THEN 1
        WHEN DATEPART(weekday, DATEADD(MONTH, -1, @ArchiveDate)) = 7 THEN 2
        ELSE 0
        END
    , DATEADD(MONTH, -1, @ArchiveDate))

What I get from this is 8/2/2019 but as you can see I want 8/1/2019.

SELECT 
    CASE 
        WHEN DATENAME(WEEKDAY, DATEADD(mm, DATEDIFF(mm, 0, @ArchiveDate) - 1, 0)) = 'Saturday'
            THEN DATEADD(mm, DATEDIFF(mm, 0, @ArchiveDate) - 1, 0) + 2
        WHEN DATENAME(WEEKDAY, DATEADD(mm, DATEDIFF(mm, 0, @ArchiveDate) - 1, 0)) = 'Sunday'
            THEN DATEADD(mm, DATEDIFF(mm, 0, @ArchiveDate) - 1, 0) + 1
        ELSE 
            DATEADD(mm, DATEDIFF(mm, 0, @ArchiveDate) - 1, 0)
    END

This will return the first BUSINESS DAY of the previous month.

Series 7 Exam For Dummies with Online Practice Tests, You would write it as 10/25 (tenth month and 25th day). 114 days of accrued interest First subtract the seventh month (July) from the tenth month (October). Subtract the previous coupon date (1) from the settlement date (25) to get an answer of However, U.S. government securities settle in one business day, not two. Subtract Month for Month Year Date Format I am using Excel 2010 and setting up a scroll bar to retrieve data by year and periods of one month which is the only way the data is available. How can I subtract one month from a date formatted as "Jan-2015" etc. Thanks for any help.

you can use eomonth along with your logic to get first day of previous month as below:

DECLARE @ArchiveDate date = '9/2/2019'

select dateadd(day, 1, eomonth(dateadd(month, -2, @ArchiveDate)));

DATEADD: Adding or Subtracting a Date Unit to or From a Date, For example, adding one month to October 31 yields November 30, not November 31 For example, if you add one day to Friday, first DATEADD moves to the next weekday, Monday, then it adds a day. Since Dialogue Manager interprets a date as alphanumeric or numeric, and To make sure it is Thursday, assign it as Subtract months from date using the Excel EDATE function. EXCEL. = EDATE (B5,-C5) This formula subtracts five (5) months from the date specified in cell (B5) using the Excel EDATE function. This formula links to specific cells in the worksheet for the formula parameters, however, you can also enter the number of months you want to subtract and the date that you want to subtract the months from directly into the formula (e.g. =EDATE ("15/03/2017",-5)).

You can use EOMONTH with DATEADD():

SELECT DATEADD(DAY, 1, EOMONTH(@ArchiveDate, -2)) AS ArchiveDate

Joe Celko's Data, Measurements and Standards in SQL, I did not want to include Thursday, I would need to subtract one in the computation. to find the first business day (n) days from a given date, I add (n) or (n 1) to the table for global time zone information that is used to make sure that temporal of days in a year is not uniform; weeks are not related to months; and so on. Problem. How to add or subtract X amount of days/months/years from a date. Solution. Use either of the follow methods: Create a result column as follows (note: MMDDYY is the date wrapper; choose the one that fits the date format on your AS/400)

Russia, In Russian business letters and documents the date is commonly given by both the Russian January 1 in both styles is — January 1/14 — the second date being with a business man in Russia has to subtract 13 days from his calendar date. The first day of any month O. S. will obviously be the 14th day of the same  Here is a way to get the next day without using a loop. It uses the fact that 2nd Jan 0001 was a Sunday. It derives the day of the week of date d, and then uses the mod function to add either 0, 1 (if current Date is Saturday) or 2 (if current date is Friday) days.

Russia: America's Greatest Export Opportunity, In Russian business letters and documents the date is commonly given by both A short way of writing the Russian January 1 in both styles is — January 1/14 — the with a business man in Russia has to subtract 13 days from his calendar date. The first day of any month O. S. will obviously be the 14th day of the same​  One could also obtain a DateTime value by taking the month and year from the current date and the either subtracting the appropriate number of days from the date to set it to the first or by creating a new DateTime variable and instancing it with the selected date's month and year along with manually setting the new date's day to "1" as shown

The Complete Compendium of Universal Knowledge: Containing All You , The Day - Book should contain statements of every business transaction , which The day , month , and year , should then be written , and repeated at the head of with the date of the first transaction on the page ; the subsequent dates on the By subtracting one side from the other the balance which is due will be found . + “ until next invoice date.”); This example works by first getting the current date, then creating a new LocalDate object that represents the 15th of the current month. Then, if the current day of the month is greater than 15, it adds one month to the invoicing date.

Comments
  • Do you want to always get the first day of the prev month?
  • Maybe you are just looking for SELECT DATEFROMPARTS(YEAR(@ArchiveDate), MONTH(@ArchiveDate)-1, 1)
  • I want to go a month back from the Archive Date and return the first business day
  • Try '7/1/2019' your code fives '6/1/2019' which is on a Saturday