Good addition nagrajm. Here is another formula also can help you: =DATE(YEAR(A2),MONTH(A2),1). If not, you want lets say you want to display the value not done. The DAY Function on its own is used to return the day of a date. Already a Member? While we know that this would be somewhere at the beginning of the month, to get the exact date, we need to find out what day it is on the first day of the month and then use it to get the first Monday. The EOMONTH function returns the last day of in the month of a given date. The tricky part is if that date falls on the 1st, then that hire plus 6 is the participation date. So the EOMONTH formula to get the first day of the month would be =EOMONTH (date,-1)+1. Our goal is to help you work faster in Excel. You need to define more carefully what the "monthly headcount" is. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps. This means that if the DOH is on the first of the month, the employee will be eligible that day. #1 Hello, I am having trouble creating a formula to capture the "first of the month after 60 days" from the Hire Date. Further details on your benefit options will be provided during your New Employee Orientation. Now, if you want to get the first day of the month from a given date, you just need to use the day value in the date and bring it back to 1. We have a great community of people providing Excel help here, but the hosting costs are enormous. Workflow? 3. Hi, can you identify the error here? In this tutorial, I will demonstrate some ways in which you can use the IF function with date values. How to Compare Two Columns in Excel (using VLOOKUP & IF), How to Center the Worksheet Horizontally on the Page in Excel, How to Merge First and Last Name in Excel, How to Remove Commas in Excel (from Numbers or Text String), Why does Excel Open on Startup (and How to Stop it), How to Unsort in Excel (Revert Back to Original Data), Same as the date with which we are comparing, Instead of hardcoding the dates into the IF functions logical test parameter, you can store the date in a separate cell and refer to it with a cell reference. Afterward, we can add 1 into the function to get the first day of the month. Note: For some special date such as 2016/10/31, you will get the result as 2016/11/30 with above formula. Follow these easy steps to disable AdBlock, Follow these easy steps to disable AdBlock Plus, Follow these easy steps to disable uBlock Origin, Follow these easy steps to disable uBlock. So this is how you can use formulas in excel to find out the first day of the month or the first Monday (or any other weekday) of the month. Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day. Open and create multiple documents in new tabs of the same window, rather than in new windows. This function is useful in formulas where you need to calculate an age. This means that adding n number of days to a date is equivalent to adding the value n to the serial number that the date represents. 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 IF function is one of the most useful Excel functions. Click here to reveal answer 1 2 Next Sort by date Sort by votes Benefits are effective as of the first of the month following your date of hire, unless hired on the first, then benefits are effective on date of hire. The date of the month you are trying to find. Back to, =IF(DAY(EDATE(A2,1))