# Solved Date Calculation in Excel

Microsoft Excel for mac 2011 - macintosh
August 6, 2016 at 00:01:45
Specs: Mac OSX 10.8.4
 How to write a formula to calculate how many 15th of the month (or any particular day in the month) has gone by between two different dates in Excel? I don't need round up or down but the exact times that has gone by. TIA.

See More: Date Calculation in Excel

August 8, 2016 at 07:39:07
 This is a completely different approach, but seems to work for all your examples.We are using SUMPRODUCT() and INDIRECT() to create an array of Dates,then counting the number of target days in the array. =SUMPRODUCT((DAY(ROW(INDIRECT(A2&":"&B2)))=C2)*1)Test it and see how it works. MIKEhttp://www.skeptic.com/

#1
August 6, 2016 at 07:36:59
 Not exactly sure what your looking for.To get the number of Days between two Dates you simply Subtract.Using the DATEDIF() function you can get Days, Months, Years, orsome combination of them.If you want Work Days, then the NETWORKDAYS() function, returns the number of whole working days between two dates excluding (if provided) specified holidays.An example of what you need might be helpful.MIKEhttp://www.skeptic.com/

Reply ↓  Report •

#2
August 6, 2016 at 11:16:38
 What I'm looking for is the exact count of a certain day in the month, could it the 4th, or the 23rd, or whatever day within a month (actually what I need is mostly15th of the month). I need to know the exact count of that day between two dates.For example: Let's say the day is 5th I'm looking for. Between Jan 1st, 2015 and Dec. 20th, 2015, there will be a total of 12 (12 times of the 5th of the month). But between Jan 10th, 2015 and Dec. 4th, 2015, there are only 10. I hope I've clarified the problem. By using the number of days in between and divided that into months will not give the correct answer. TIA.

Reply ↓  Report •

#3
August 6, 2016 at 13:34:25
 OK, with your data like:``` A B C D 1) Date Start Date End Day Number Total 2) January 1, 2015 December 20, 2015 5 12 3) January 10, 2015 December 4, 2015 5 10 ```Put the Day Number in cell C2, ie the 5th of the monthin cell D2 enter the formula:=DATEDIF(A2, B2, "M") +1-IF(AND(DAY(A2) C2),0, 1)See how that works for you.I'm not completely sure, but it seems to work.MIKEmessage edited by mmcconaghy

Reply ↓  Report •

Related Solutions

#4
August 6, 2016 at 23:13:25
 Hi Mike,This is awesome, that's almost as what I'm looking for. The only problem is when let's say the dates are January 4, 2016 and February 5, 2016 and the Day Number is 5. The Total should be 2 instead of 1. I'm not sure how to modify the formula in order to give the correct answer. I guess I should have described the two dates are "not in between, but inclusive". Thanks again in advance.

Reply ↓  Report •

#5
August 7, 2016 at 08:19:47
 Try this, =(DATEDIF(A2,B2,"M")+1)-IF(AND(C2>=DAY(A2),C2<=DAY(B2)),0,1)We simply changed the Greater/Less Than symbols: < & >to Greater/Less Than or Equals symbols: <= & >=MIKEhttp://www.skeptic.com/

Reply ↓  Report •

#6
August 7, 2016 at 08:44:49
 Here is a modified version that first checks to make sure all the data we need is entered, before trying to do any calculations.=IF(OR(A2="",B2="",C2=""),"",(DATEDIF(A2,B2,"M")+1)-IF(AND(C2>=DAY(A2),C2<=DAY(B2)),0,1))MIKEmessage edited by mmcconaghy

Reply ↓  Report •

#7
August 7, 2016 at 10:21:40
 Hi Mike,Thanks for the updated formula. It worked much better than the first one. But when I tried it with various input and found occasions that it calculated incorrectly as shown in examples below:Date Start Date End Day Number Total Correct TotalJanuary 6, 2016 February 5, 2016 5 0 1February 6, 2016 April 5, 2016 5 1 2March 5, 2016 April 4, 2016 5 0 1I'm not sure where adjustments need to be made to the formula in order to correct this error. Thanks again.

Reply ↓  Report •

#8
August 7, 2016 at 10:23:15
 Sorry Mike I forgot how to copy and paste a spreadsheet to have it spaced properly.

Reply ↓  Report •

#9
August 7, 2016 at 10:52:07
 Use the pre tags to align your data.Click here for instructions:

Reply ↓  Report •

#10
August 7, 2016 at 11:36:48
 OK, This one is the long version and should cover all instances:=IF(OR(A2="",B2="",C2=""),"",(DATEDIF(A2,B2,"M")+1)-IF(AND(C2>=DAY(A2),C2<=DAY(B2)),0,IF(AND(C2<=DAY(A2),C2<=DAY(B2)),0,IF(AND(C2>=DAY(A2),C2>=DAY(B2)),0,IF(AND(C2<=DAY(A2),C2<=DAY(B2)),0,1)))))You may want to just Cut & Paste the formula.If you find any more exceptions let me know and we'll see about correcting them.Also, when posting examples of your spread sheet it is best to use the < PRE > tags.See this How-To which explains the use of the tags to align your data correctly.http://www.computing.net/howtos/sho...MIKEhttp://www.skeptic.com/

Reply ↓  Report •

#11
August 7, 2016 at 13:37:14
 Hi Mike,Absolutely awesome and wonderful! It works exactly the way I was expecting it to work. I can't thank you enough for all your time and effort. Also I will post spread sheet the proper way in the future, thanks again. Have a nice day!

Reply ↓  Report •

#12
August 7, 2016 at 14:31:59
 Hi Mike,Sorry to have to bother once more. But I found a consistent error after I tested it further. I used "15" as the day number since that's what I will be using mostly. I found it started counting as 1 before it came across the first 15th of the month, then it counted correctly when it did get to the 15th of the month and then it increased the count by 1 every time it started a new month instead wait until it actually gets to the next 15th of the following month. The problem appears to have something to do with the change of month at the "Date End".Please kindly see what can be done to fix this problem. Sorry that I posted so much data in order to show you when the number actually changed. Thank you.``` Date Start Date End Day Number Total Shoule Be January 1, 2016 January 2, 2016 15 1 0 January 1, 2016 January 3, 2016 15 1 0 January 1, 2016 January 4, 2016 15 1 0 January 1, 2016 January 5, 2016 15 1 0 January 1, 2016 January 6, 2016 15 1 0 January 1, 2016 January 7, 2016 15 1 0 January 1, 2016 January 8, 2016 15 1 0 January 1, 2016 January 9, 2016 15 1 0 January 1, 2016 January 10, 2016 15 1 0 January 1, 2016 January 11, 2016 15 1 0 January 1, 2016 January 12, 2016 15 1 0 January 1, 2016 January 13, 2016 15 1 0 January 1, 2016 January 14, 2016 15 1 0 January 1, 2016 January 15, 2016 15 1 January 1, 2016 January 16, 2016 15 1 January 1, 2016 January 17, 2016 15 1 January 1, 2016 January 18, 2016 15 1 January 1, 2016 January 19, 2016 15 1 January 1, 2016 January 20, 2016 15 1 January 1, 2016 January 21, 2016 15 1 January 1, 2016 January 22, 2016 15 1 January 1, 2016 January 23, 2016 15 1 January 1, 2016 January 24, 2016 15 1 January 1, 2016 January 25, 2016 15 1 January 1, 2016 January 26, 2016 15 1 January 1, 2016 January 27, 2016 15 1 January 1, 2016 January 28, 2016 15 1 January 1, 2016 January 29, 2016 15 1 January 1, 2016 January 30, 2016 15 1 January 1, 2016 January 31, 2016 15 1 January 1, 2016 February 1, 2016 15 2 1 January 1, 2016 February 2, 2016 15 2 1 January 1, 2016 February 3, 2016 15 2 1 January 1, 2016 February 4, 2016 15 2 1 January 1, 2016 February 5, 2016 15 2 1 January 1, 2016 February 6, 2016 15 2 1 January 1, 2016 February 7, 2016 15 2 1 January 1, 2016 February 8, 2016 15 2 1 January 1, 2016 February 9, 2016 15 2 1 January 1, 2016 February 10, 2016 15 2 1 January 1, 2016 February 11, 2016 15 2 1 January 1, 2016 February 12, 2016 15 2 1 January 1, 2016 February 13, 2016 15 2 1 January 1, 2016 February 14, 2016 15 2 1 January 1, 2016 February 15, 2016 15 2 January 1, 2016 February 16, 2016 15 2 January 1, 2016 February 17, 2016 15 2 January 1, 2016 February 18, 2016 15 2 January 1, 2016 February 19, 2016 15 2 January 1, 2016 February 20, 2016 15 2 January 1, 2016 February 21, 2016 15 2 January 1, 2016 February 22, 2016 15 2 January 1, 2016 February 23, 2016 15 2 January 1, 2016 February 24, 2016 15 2 January 1, 2016 February 25, 2016 15 2 January 1, 2016 February 26, 2016 15 2 January 1, 2016 February 27, 2016 15 2 January 1, 2016 February 28, 2016 15 2 January 1, 2016 February 29, 2016 15 2 January 1, 2016 March 1, 2016 15 3 2 January 1, 2016 March 2, 2016 15 3 2 January 1, 2016 March 3, 2016 15 3 2 January 1, 2016 March 4, 2016 15 3 2 January 1, 2016 March 5, 2016 15 3 2 January 1, 2016 March 6, 2016 15 3 2 January 1, 2016 March 7, 2016 15 3 2 January 1, 2016 March 8, 2016 15 3 2 January 1, 2016 March 9, 2016 15 3 2 January 1, 2016 March 10, 2016 15 3 2 January 1, 2016 March 11, 2016 15 3 2 January 1, 2016 March 12, 2016 15 3 2 January 1, 2016 March 13, 2016 15 3 2 January 1, 2016 March 14, 2016 15 3 2 January 1, 2016 March 15, 2016 15 3 January 1, 2016 March 16, 2016 15 3 January 1, 2016 March 17, 2016 15 3 January 1, 2016 March 18, 2016 15 3 January 1, 2016 March 19, 2016 15 3 January 1, 2016 March 20, 2016 15 3 January 1, 2016 March 21, 2016 15 3 January 1, 2016 March 22, 2016 15 3 January 1, 2016 March 23, 2016 15 3 January 1, 2016 March 24, 2016 15 3 January 1, 2016 March 25, 2016 15 3 January 1, 2016 March 26, 2016 15 3 January 1, 2016 March 27, 2016 15 3 January 1, 2016 March 28, 2016 15 3 January 1, 2016 March 29, 2016 15 3 January 1, 2016 March 30, 2016 15 3 January 1, 2016 March 31, 2016 15 3 January 1, 2016 April 1, 2016 15 4 3 January 1, 2016 April 2, 2016 15 4 3 January 1, 2016 April 3, 2016 15 4 3 January 1, 2016 April 4, 2016 15 4 3 January 1, 2016 April 5, 2016 15 4 3 January 1, 2016 April 6, 2016 15 4 3 January 1, 2016 April 7, 2016 15 4 3 January 1, 2016 April 8, 2016 15 4 3 January 1, 2016 April 9, 2016 15 4 3 January 1, 2016 April 10, 2016 15 4 3 January 1, 2016 April 11, 2016 15 4 3 January 1, 2016 April 12, 2016 15 4 3 January 1, 2016 April 13, 2016 15 4 3 January 1, 2016 April 14, 2016 15 4 3 January 1, 2016 April 15, 2016 15 4 January 1, 2016 April 16, 2016 15 4 ```

Reply ↓  Report •

#13
August 7, 2016 at 16:44:27
 OK, I see the problem.I may have to rethink this solution, The DATEDIF() function does not include the ending Month, so you don't get a 12 month return unless your dates are:``` 01-01-2016 & 01-01-2017 ```So the DATEDIF() is returning a number that is one month less than what I thought.I'll work on it and get back to you.MIKEhttp://www.skeptic.com/

Reply ↓  Report •

#14
August 7, 2016 at 17:01:11
 Thanks Mike, take your time and no need to rush. I do appreciate you quick response.

Reply ↓  Report •

#15
August 7, 2016 at 17:51:22
 OK, let's try brute force:=IF(YEAR(A1) =YEAR(B1),IF(AND(DAY(A1)<15, DAY(B1)>=15),MONTH(B1)-MONTH(A1)+1,MONTH(B1)-MONTH(A1)),(YEAR(B1)-YEAR(A1) )*12+IF(AND(DAY(A1)<15, DAY(B1)>=15),MONTH(B1)-MONTH(A1)+1,MONTH(B1)-MONTH(A1)))

Reply ↓  Report •

#16
August 7, 2016 at 18:35:22
 Hi Mike,I've tested the new one and it worked very well until I used a different Date Start such as 15th or higher, then it gave a wrong count. And just like before; it changed at the beginning of a new month at the Date End instead of on the 15th.I feel terrible as if I've taken your weekend away. I do sincerely and very much appreciate what you've done so far. I can't thank you enough.```Date Start Date End Total Should Be January 15, 2016 January 16, 2016 0 1 January 15, 2016 January 17, 2016 0 1 January 15, 2016 January 18, 2016 0 1 January 15, 2016 January 19, 2016 0 1 January 15, 2016 January 20, 2016 0 1 January 15, 2016 January 21, 2016 0 1 January 15, 2016 January 22, 2016 0 1 January 15, 2016 January 23, 2016 0 1 January 15, 2016 January 24, 2016 0 1 January 15, 2016 January 25, 2016 0 1 January 15, 2016 January 26, 2016 0 1 January 15, 2016 January 27, 2016 0 1 January 15, 2016 January 28, 2016 0 1 January 15, 2016 January 29, 2016 0 1 January 15, 2016 January 30, 2016 0 1 January 15, 2016 January 31, 2016 0 1 January 15, 2016 February 1, 2016 1 January 15, 2016 February 2, 2016 1 January 15, 2016 February 3, 2016 1 January 15, 2016 February 4, 2016 1 January 15, 2016 February 5, 2016 1 January 15, 2016 February 6, 2016 1 January 15, 2016 February 7, 2016 1 January 15, 2016 February 8, 2016 1 January 15, 2016 February 9, 2016 1 January 15, 2016 February 10, 2016 1 January 15, 2016 February 11, 2016 1 January 15, 2016 February 12, 2016 1 January 15, 2016 February 13, 2016 1 January 15, 2016 February 14, 2016 1 January 15, 2016 February 15, 2016 1 2 January 15, 2016 February 16, 2016 1 2 January 15, 2016 February 17, 2016 1 2 January 15, 2016 February 18, 2016 1 2 January 15, 2016 February 19, 2016 1 2 January 15, 2016 February 20, 2016 1 2 January 15, 2016 February 21, 2016 1 2 January 15, 2016 February 22, 2016 1 2 January 15, 2016 February 23, 2016 1 2 January 15, 2016 February 24, 2016 1 2 January 15, 2016 February 25, 2016 1 2 January 15, 2016 February 26, 2016 1 2 January 15, 2016 February 27, 2016 1 2 January 15, 2016 February 28, 2016 1 2 January 15, 2016 February 29, 2016 1 2 January 15, 2016 March 1, 2016 2 January 15, 2016 March 2, 2016 2 January 15, 2016 March 3, 2016 2 January 15, 2016 March 4, 2016 2 January 15, 2016 March 5, 2016 2 January 15, 2016 March 6, 2016 2 January 15, 2016 March 7, 2016 2 January 15, 2016 March 8, 2016 2 January 15, 2016 March 9, 2016 2 January 15, 2016 March 10, 2016 2 January 15, 2016 March 11, 2016 2 January 15, 2016 March 12, 2016 2 January 15, 2016 March 13, 2016 2 January 15, 2016 March 14, 2016 2 January 15, 2016 March 15, 2016 2 3 January 15, 2016 March 16, 2016 2 3 January 15, 2016 March 17, 2016 2 3 January 15, 2016 March 18, 2016 2 3 January 15, 2016 March 19, 2016 2 3 January 15, 2016 March 20, 2016 2 3 January 15, 2016 March 21, 2016 2 3 January 15, 2016 March 22, 2016 2 3 January 15, 2016 March 23, 2016 2 3 January 15, 2016 March 24, 2016 2 3 January 15, 2016 March 25, 2016 2 3 January 15, 2016 March 26, 2016 2 3 January 15, 2016 March 27, 2016 2 3 January 15, 2016 March 28, 2016 2 3 January 15, 2016 March 29, 2016 2 3 January 15, 2016 March 30, 2016 2 3 January 15, 2016 March 31, 2016 2 3 January 15, 2016 April 1, 2016 3 January 15, 2016 April 2, 2016 3 January 15, 2016 April 3, 2016 3 January 15, 2016 April 4, 2016 3 January 15, 2016 April 5, 2016 3 January 15, 2016 April 6, 2016 3 January 15, 2016 April 7, 2016 3 January 15, 2016 April 8, 2016 3 January 15, 2016 April 9, 2016 3 January 15, 2016 April 10, 2016 3 ```

Reply ↓  Report •

#17
August 7, 2016 at 20:02:52
 uhjb,If you are referring the "new one" in Response #15, that wasn't from Mike, it was from me.Try this one. Ugly, but I think it works. Your testing will tell us if it does or not.=IF(AND(YEAR(A2) =YEAR(B2),DAY(A2)=15, DAY(B2)=15),0,IF(AND(YEAR(A2) <>YEAR(B2),DAY(A2)=15, DAY(B2)=15),(YEAR(B2)-YEAR(A2) )*12,IF(YEAR(A2) =YEAR(B2),IF(AND(DAY(A2)<=15, DAY(B2)>=15),MONTH(B2)-MONTH(A2)+1,MONTH(B2)-MONTH(A2)),(YEAR(B2)-YEAR(A2) )*12+IF(AND(DAY(A2)<=15, DAY(B2)>=15),MONTH(B2)-MONTH(A2)+1,MONTH(B2)-MONTH(A2)))))

Reply ↓  Report •

#18
August 7, 2016 at 21:00:40
 Hi DerbyDad03,I'm so sorry, lost track of who's helping me here. I'm certainly grateful for your time and effort in helping with this problem.Your new formula worked in almost all cases, except for when the Date Start and Date End happens to be both on 15th as shown below. In fact the result was all "0". It worked with any other combinations, except for 15th & 15th. TIA.```Date Start Date End Total Should Be January 15, 2016 January 15, 2016 0 1 January 15, 2016 February 15, 2016 0 2 January 15, 2016 March 15, 2016 0 3 January 15, 2016 April 15, 2016 0 4```

Reply ↓  Report •

#19
August 8, 2016 at 07:39:07
 This is a completely different approach, but seems to work for all your examples.We are using SUMPRODUCT() and INDIRECT() to create an array of Dates,then counting the number of target days in the array. =SUMPRODUCT((DAY(ROW(INDIRECT(A2&":"&B2)))=C2)*1)Test it and see how it works. MIKEhttp://www.skeptic.com/

Reply ↓  Report •

#20
August 8, 2016 at 11:18:16
 👍 Awesome…this one totally works like a charm. It passed all tests and I really appreciate all your time and effort. I couldn't never have done this without your help. I've never even heard of "SUMPRODUCT". Anyway thanks again to Mike and DerbyDad03. Have yourself a wonderful day!

Reply ↓  Report •

#21
August 8, 2016 at 12:30:16
 Nice job, Mike! Short and sweet.

Reply ↓  Report •