But we dont want the last day of the month we want the first day. I'm working on similar Form and trying to calculate end ofthe month to populate data field. In Excel, to get the month's first day, you can use a formula. You can use the IF function along with the TODAY function in cell B2 as follows: Heres a screenshot to illustrate the effect of the above formula (assuming the current date is 05/08/2021): An interesting thing about dates in Excel is that you can perform addition and subtraction operations with them too. I had forgotten about the 'first of month' exception. =EOMONTH(Date ,1 )+1, where "Date" was the variable for the employment start date. How to Get the First Day Of The Month In Excel? You are not allowed to follow up in this post. Tools, AddIns, should give you a list of Add Ins which are available, including the Analysis Toolpak. How to Convert Serial Numbers to Date in Excel. I've learned so much over the past year because of your site's clear laid out explanations. Returns the serial number of a particular date. Our policy states that insurance becomes effective on the first day of the month after the employee has been employed a full 30 days. If your start date with Yale is before your scheduled New Hire Orientation date, or if you have questions regarding benefit option elections, please bring your Net ID and password with you for online enrollment help at the end of the orientation session. This function converts a date in text format to a serial number that Excel can recognize as a date. Learn Excel with high quality video training. You are using an out of date browser. But if the first day of the month is not a Monday, then the MOD formula would give us the total number of days that we should add to this date to get the first Monday of the month. So I tried it with 90 days, =(A1+90)-DAY(A1+90)+1 and it worked! Additionally, we can also get the first day of the month based on a given month's name. This means that if the DOH is on the first of the month, the employee will be eligible that day. In this tutorial, I will demonstrate some ways in which you can use the IF function with date values. 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))
