Solved

Calculate days between two dates

Posted on 2016-11-24
7
147 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 33

Accepted Solution

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

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 33

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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 12

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 33

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

Do you have a plan for Continuity?

It's inevitable. People leave organizations creating a gap in your service. That's where Percona comes in.

See how Pepper.com relies on Percona to:
-Manage their database
-Guarantee data safety and protection
-Provide database expertise that is available for any situation

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

630 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