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

Reply ↓  Report •


✔ Best Answer
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.

MIKE

http://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, or
some 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.

MIKE

http://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 month
in cell D2 enter the formula:

=DATEDIF(A2, B2, "M") +1-IF(AND(DAY(A2) <C2,DAY(B2)>C2),0, 1)

See how that works for you.
I'm not completely sure, but it seems to work.

MIKE

http://www.skeptic.com/

message 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: <= & >=

MIKE

http://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))

MIKE

http://www.skeptic.com/

message 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 Total
January 6, 2016 February 5, 2016 5 0 1
February 6, 2016 April 5, 2016 5 1 2
March 5, 2016 April 4, 2016 5 0 1

I'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:

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


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...

MIKE

http://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.

MIKE

http://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)))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


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)))))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


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
✔ Best Answer
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.

MIKE

http://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.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •


Ask Question