The trickiest calculation is the number of months because of how you can either round up or round down depending on whether it’s a complete month or a partial month. Luckily, you can see the syntax and all supported arguments for the DATEDIF function here.Īs an added bonus, what if you wanted to calculate the number of weekdays between two dates? That’s easy enough also because we have a built-in formula: =NETWORKDAYS(startDate,endDate)Ĭalculating the number of days and weekdays is simple enough, so let’s talk about months now. When you type the formula, you’ll see that Excel does not give you any suggestions for the fields, etc. Lastly, you can use a function called DATEDIF, which is included in Excel from Lotus 1-2-3 days, but isn’t an officially supported formula in Excel. If you switch the dates in the formula, you’ll just get a negative number. This function takes two arguments: the end date and the start date. In addition, you can also use the DAYS function. Excel knows it’s a date and simply calculates the number of days between those two dates. The first formula is just a simple subtraction of the two dates, A3 – A2. In Excel, there are always multiple ways to calculate the same value and that’s what I did here. At the right, you can see the difference between those two dates is 802 days.

In my example above, I have two dates stored in cells A2 and A3. The good thing about calculating days is that there really is only one way to calculate the value, so you don’t have to worry about different formulas giving you different answers. The simplest calculation we can do is to get the number of days between two dates. There may be other, simpler, ways of doing this but it has developed as your requirements have changed. You might need to adjust the breakpoint of 4/24 to meet your specific requirements. In your example, the person had a full day on the first day, 8 hours and half a day on the second day, a total of 12 hours, not, as you indicated, 14 hours.

But who knows if the person worked through lunch, started early, finished late, etc. Similarly, if the finish time was between 12:30 and 1:30, default the end time to 1:30. I think, for the first day, you'd have to check if the start time was logged as between 12:30 and 1:30 and default the start to 1:30.

multiply by 8 instead of 9.įor the first and last days, it is more complicated as you'll need to determine if the leave (?) started before or after lunch on the first day and if it finished before or after lunch on the last day. Well, for the full days, it's not a problem. How do i modify the formula if i also need to exclude the lunch hour between 12.30 pm and 1.30 pm?

Excel date formula subtract days