A combination of the functions add-months and first-of-month will give you what you want without having to parse the date down into day, months, and year. For example, if you want to find the first Saturday of any month you can tweak the formula as shown below: The only change I have made is the second argument of the WEEKDAY formula. The following formula can help you to get same day of next month based on given date in Excel. For full access, please, Please wait while this file loads, or download it. Press CTRL + 1. Important:The calculated results of formulas and some Excel worksheet functions may differ slightly between a Windows PC using x86 or x86-64 architecture and a Windows RT PC using ARM architecture. The Decision Support GroupReporting Consulting with Cognos BI Tools"Magic with Data"www.decisionsupportgroup.com. Using a hire date of 7/1/17 my script results in 7/31/17, I need to calculate it to 8/1/17. The tricky part is if that date falls on the 1st, then that hire plus 6 is the participation date. Here are some handy formulas can help you to convert a random date to the first day of the month, please do as follows: Enter this formula: =EOMONTH(A2,-1)+1 into a blank cell where you want to output the result, and then drag the fill handle down to the cells which you want to apply this formula, and all dates have been converted to the first day of month, see screenshot: 1. Suppose I have a data set as shown below where I have the dates in column A and I want to get the first day of the month for each of these dates. These values can be numbers, text, or . You are using an out of date browser. To set up a free one-on-one session with a TIAA advisor to explore fund options and complete enrollment process, call 1-800-732-8353. I knew you all would have a better solution, you are the best! So I tried it with 90 days, =(A1+90)-DAY(A1+90)+1 and it worked! 2. Let me take an example of September 2020 where the first day of the month is a Tuesday in the first Monday is on 8 September 2020. I'm trying to figure out how to create a formula to show result of the following; Undo last command with Ctrl+Z or Alt+Backspace. For getting the first day of next month based on given date in Excel, the following formula can help you. In this Excel tutorial, Ill show you some really simple formula that you can use to get the first day of a month in Excelbased on a given date. The above formula uses the date in cell A2 (which is stored as a serial number in the back and in Excel) and subtracts the day value of the same date (which is given by the DAY formula). If you put a date within quotes, it is essentially a text or string value. Note:Version markers indicate the version of Excel a function was introduced. The given date will be in its own cell (C34) but the resulting date will be part of a concatenated formula and I will like it displayed in "00/00/00" format. Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. If you really want to get the same date of next month based on date 2016/10/31, please use this formula =IF(DAY(EDATE(A2,1))