Solved

Calculate days between two dates

Posted on 2016-11-24
7
38 Views
Last Modified: 2016-11-24
Hi , i'm using this code which calulates the number of days between two dates however if the two dates are the same day i get = 0. for eample:

24/11/2016 to 24/11/2016 = 0

I need it to say:

24/11/2016 to 24/11/2016 = 1

or another example:

24/11/2016 to 25/11/2016 = 2

targetSheet.getRange(targetSheet.getLastRow(),numColumns  + 1).setFormula("=DATEDIF(R[0]C[-8],R[0]C[-1], \"D\")");

Open in new window


thanks you
zac
0
Comment
Question by:bede123
  • 3
  • 2
  • 2
7 Comments
 
LVL 31

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 41900502
Just do +1 on the end of the formula
0
 
LVL 11

Expert Comment

by:Missus Miss_Sellaneus
ID: 41900504
Use MAX with a 1 around the DATEDIF formula like this:

=MAX(1,DATEDIF(R[0]C[-8],R[0]C[-1], \"D\")"))
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41900506
@Miss_Sellaneus - Using MAX would get round the same day issue, making the 0 become 1 but it wouldn't get round the other example, that would still say 1 rather than 2.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 11

Expert Comment

by:Missus Miss_Sellaneus
ID: 41900509
Yes, I know.

In my hurry to answer I missed that the author wanted that for all of it.
0
 
LVL 1

Author Closing Comment

by:bede123
ID: 41900541
thank you very much.  my code now looks like this and works:

targetSheet.getRange(targetSheet.getLastRow(),numColumns  + 1).setFormula("=DATEDIF(R[0]C[-8],R[0]C[-1],\"D\")+1");
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41900556
To expand a bit on how it works.....

Excel stores dates as an integer serial number based on the number of days since the start of time; the start of time being 01 Jan 1900 in Excel's brain.  Therefore, to calculate the number of days between two dates you have to deduct the earlier date from the later date.

Today (24 Nov 16) is day 42698, yesterday was therefore 42697. To calculate number of days you would have a formula that says "=Today minus Yesterday" which excel reads as "=42698-42697" and the result is obviously 1.  

When working with dates it is quite common to have to adjust the result by 1. Some examples:
1) If looking at a working week you would say that Monday to Friday is 5 days yet if you had Friday Date minus Monday Date the result would be 4; need to add 1.

2) If an event was being held over a weekend, we would say it was 2 days; but Sunday Date minus Saturday Date = 1, need to add 1.

Excel gets even more complicated when time is also involved but that's another topic!!!
0
 
LVL 1

Author Comment

by:bede123
ID: 41900590
thanks. makes so much more sense when written in plain English.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Boost your ability to deliver ambitious and competitive web apps by choosing the right JavaScript framework to best suit your project’s needs.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
In this Experts Exchange video Micro Tutorial, I'm going to show how small business owners who use Google Apps can save money by setting up what is called a catch-all email address in their Gmail accounts. By using the catch-all feature, small busin…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now