# How To Calculate the Number of Days Between Two Dates

#### Mahares

##### Expert
Longtime User
I am puzzled by the following code I wrote to compute the number of days between the current date and time, and a previous date and time: It returns exactly 1 day less than it should be. But, when I replace the line that starts with MyDate with the commented line and insert the current date and time instead of the variables, it returns the correct numbers of days between. Any tips will be greatly appreciated. Thank you.
B4X:
``````Dim MyDateTime As Long
Dim txtDate, txtTime As String
Dim Datepr, MyDate As Long
Dim Ndays as double

MyDateTime = DateTime.Now
DateTime.DateFormat = "M/d/yyyy"     'example: 12/28/2011
DateTime.TimeFormat="h:mm:ss a"      'example: 6:29:17 PM
txtDate=DateTime.Date(MyDateTime)
txtTime=DateTime.Time(MyDateTime)
Msgbox(txtDate & "  " & txtTime,"")
Datepr = DateTime.DateParse("12/24/2011") + DateTime.TimeParse("6:29:17 PM")
'MyDate = DateTime.DateParse("12/28/2011") + DateTime.TimeParse("6:29:17 PM")   'You enter the current date and time manually here.
MyDate = DateTime.DateParse(txtDate) + DateTime.TimeParse(txtTime)
Ndays=Round2((MyDate - Datepr)/1000/3600/24,1)
Msgbox(Ndays,"")``````

#### Erel

##### B4X founder
Staff member
Longtime User
B4X:
``DateTime.TimeParse("6:29:17 PM")``
TimeParse returns the the value of the given time on the current date.

This means that the following line is wrong:
B4X:
``DateTime.DateParse("12/24/2011") + DateTime.TimeParse("6:29:17 PM")``

Try this code:
B4X:
``````Sub Activity_Create(FirstTime As Boolean)
DateTime.SetTimeZone(0)
Dim now, past As Long
now = DateTime.Now
DateTime.DateFormat = "M/d/yyyy"      'example: 12/28/2011
DateTime.TimeFormat="h:mm:ss a"
past = DateTime.DateParse("12/24/2011") + (DateTime.TimeParse("6:29:17 PM") Mod DateTime.TicksPerDay)

Log(DaysBetweenDates(past, now))
End Sub

Sub DaysBetweenDates(Date1 As Long, Date2 As Long)
Return Floor((Date2 - Date1) / DateTime.TicksPerDay)
End Sub``````

#### nfordbscndrd

##### Well-Known Member
Longtime User
B4X:
``DateTime.TimeParse("6:29:17 PM")``
TimeParse returns the the value of the given time on the current date.

This means that the following line is wrong:
B4X:
``DateTime.DateParse("12/24/2011") + DateTime.TimeParse("6:29:17 PM")``

I don't understand why the above is wrong. I use it in the following routine in the Wiki and it seems to work:

B4X:
`````` Dim seconds, minutes, hours, days As Int
Dim date1, date2, elapsed As Long
date1 = DateTime.DateParse("02/01/2013") + DateTime.TimeParse("08:00:00")
date2 = DateTime.DateParse("03/01/2013") + DateTime.TimeParse("00:00:01")
elapsed = date2 - date1
seconds = Round(elapsed / 1000)
minutes = Floor(seconds / 60) Mod  60
hours = Floor(seconds / 3600)
days = Floor(hours / 24)
hours = hours Mod 24
seconds = seconds Mod 60
Log(days & ", " & hours & ":" & minutes & ":" & seconds)``````

#### Erel

##### B4X founder
Staff member
Longtime User
While it is wrong, it still returns the correct result in this case as you are adding the same wrong value to both date1 and date2.

You can use it this way. Just make sure that you understand that DateTime.Date(date1) will return the year 4015.

#### Mahares

##### Expert
Longtime User
I tweaked Erel's code and mine to come up with a meaningful result. Unfortunately, either there is something too complex and mysterious about the date and time functions or I am not grasping the concept. I made the previous date so it yields exactly 2 days, but it yielded something totally different. I still need some clarification. There has to be an easier way to arrive at a simple subtraction between dates. Thank you.

Dim Ndays as double 'days between dates with 2 decimals
Dim Datepr, MyDate As Long 'past and current dates
Dim txtDate, txtTime As String ''past date and time as variables
'DateTime.SetTimeZone(0) 'GMT
DateTime.SetTimeZone(-5) 'Eastern USA
DateTime.DateFormat = "M/d/yyyy" 'example: 12/28/2011
DateTime.TimeFormat="h:mm:ss a"
MyDate = DateTime.Now
txtDate="12/27/2011" :txtTime="9:28:12 AM"
Datepr = DateTime.DateParse(txtDate) + (DateTime.TimeParse(txtTime) Mod DateTime.TicksPerDay)
Ndays= Round2((MyDate - Datepr) / DateTime.TicksPerDay,2)
Msgbox(Ndays,"")

#### Erel

##### B4X founder
Staff member
Longtime User
Try to run my code exactly as it is. If it doesn't work please post the values that you assigned.

#### Mahares

##### Expert
Longtime User
What does it take to come up with exactly the following scenario:
time zone Eastern US and I would like the previous date to be 12/27/2011 and the previous time to be exactly the current time , so the result yields exactly 2.0 days (without truncation). My current date is of course: 12/29/2011.
Your code only worked when I added 5 hours to the time. For instance,
DateTime.TimeParse("11:04:17 AM") in your code would be changed to:
DateTime.TimeParse("4:04:17 PM").
But, I am interested in a different time zone: Eastern US. That is why I had DateTime.SetTimeZone(-5) in my code.
Thanks

#### Erel

##### B4X founder
Staff member
Longtime User
This code is simpler and allows you to set the time zone:
B4X:
``````Sub Activity_Create(FirstTime As Boolean)
DateTime.SetTimeZone(0)
Dim Date1, Date2 As Long
DateTime.DateFormat = "M/d/yyyy h:mm:ss a Z"
Date1 = DateTime.DateParse("12/29/2011 11:04:17 AM -5")
Date2 = DateTime.DateParse("12/27/2011 11:04:17 AM -5")
Log(DaysBetweenDates(Date1, Date2))
End Sub

Sub DaysBetweenDates(Date1 As Long, Date2 As Long)
Return Floor((Date2 - Date1) / DateTime.TicksPerDay)
End Sub``````

#### Mahares

##### Expert
Longtime User
I am sorry, your code does not address the problem. I am sorry to be persistent. Here are exactly my conditions:
1. The current date and current time must be the device current date and time and they must be stored in 2 separate variables: txtDate (exple: 12/30/2011) and txtTime (exple: 5:45:23 PM). They are not fixed. They vary with the time of day.
2. The previous date and time are also stored in 2 separate variables and not fixed: txtDatepr (exple: 12/16/2011) and txtTimepr (exple: 11:17:45 AM). They are extracted from variables which in turn are extracted from a a date field and a time field in a table.
3. The result which is the number of days between them must be a number with 1 or 2 decimals. Using FLOOR masks the results. I need an accurate result because it is used as a variable in subsequent calculations.

#### Smee

##### Well-Known Member
Longtime User
my 2 cents fwiw

I use this routine to find the time period elapsed between today and a particular files date

B4X:
``````   d = File.LastModified(Directory, FileName)
DateDiff=s.DateNOD(DateTime.Date(DateTime.Now),DateTime.Date(d))
If DateDiff>42 Then
File.Delete(Directory,FileName)
End If``````

s.datenod is a code routine used in a code module provided here on the forum

the actual routine looks like this

B4X:
``````Sub DateNOD(CurrentDate As String, OtherDate As String) As Int
Dim CurrDate, OthDate, MyNewDate As Long
CurrDate = DateTime.DateParse(CurrentDate)
OthDate = DateTime.DateParse(OtherDate)
NumOfDays = (CurrDate-OthDate)/(DateTime.TicksPerDay)
Return NumOfDays
End Sub``````

This works fine for my purposes. perhaps you could rework it to your needs

#### Mahares

##### Expert
Longtime User
My project requires that I add the time element also to the calculations. Therefore, it is not a simple subtraction between two dates. Here is a simple example for illustration:
Previous date=12/27/2011 , Previous time=4:35:16 PM
Current date from device=12/31/2011 , Current time from device=9:15:12 AM.
The difference between the two dates is not 4 days. It is around 3.69 days. I have tried to add Timeparse into the mix, but it did not yield correct results. I run my code at different times of the day, the days between the 2 dates change of course and you expect them to, because the current time and date change, but the answer is not mathematically correct.
I hope this simple example can explain my situation better.
Thank you

#### bees

##### Member
Longtime User
Previous date=12/27/2011 , Previous time=4:35:16 PM
Current date from device=12/31/2011 , Current time from device=9:15:12 AM.
The difference between the two dates is not 4 days. It is around 3.69 days.

It took me a while to understand what you needed.
You could use the build in sqlite functions:

B4X:
``````Dim oSql             As SQL  'Only if you do not use sqlite
Dim oRst             As Cursor
Dim cQuery          As String
Dim fDaysBetween As Double

oSql.Initialize("","",False) 'Create dummy connection if you do not use sqlite

cQuery = "SELECT julianday('2011-12-31 09:15:12') - julianday('2011-12-27 16:35:16') as DaysBetween"
oRst    = oSql.ExecQuery(cQuery)
If oRst.RowCount > 0 Then
oRst.Position = 0
fDaysBetween = oRst.GetDouble("DaysBetween")
End If``````

This will result in:
fDaysBetween = 3.69439814798534

Just make sure you use the right date and time format as in the sample code.

Happy new year

Stephen

#### Mahares

##### Expert
Longtime User
Stephen: I tried your code as you have and it works. However, my situation is different:
The previous date and previous time are extracted from a table and they are stored in 2 separate variables (txtDatepr) and (txtTimepr). The same thing goes for the current date and time of the device. I extract them from 2 separate variables also (txtDate and txtTime).

MyDate = DateTime.Now 'This is current device date
txtDate=DateTime.Date(MyDate) 'This is current device date converted to a string that will eventually be strored in an SQL table as a separate field.
txtTime=DateTime.Time(MyDate) 'This is current device time converted to a string that will eventually be strored in an SQL table as a separate field.
Happy new year

#### DarkMann

##### Member
Longtime User
Can you not just use your two fields joined together in any of the above methods.

Stephen's routine above seems to do what you want for accuracy. Using that as a starting point, build your query string to use the date and time that you need. I haven't tried any of the other suggestions, but since they return a TICKS value they should all give the same result

B4X:
``queryTime=txtDate & " " & txtTime``

then, to get your level of accuracy to 2 decimals you can use Round2

B4X:
``fDaysBetween=Round2(fDaysBetween,2)``

Hope that helps,

David

#### bees

##### Member
Longtime User
Stephen: I tried your code as you have and it works. However, my situation is different:
The previous date and previous time are extracted from a table and they are stored in 2 separate variables (txtDatepr) and (txtTimepr). The same thing goes for the current date and time of the device. I extract them from 2 separate variables also (txtDate and txtTime).

MyDate = DateTime.Now 'This is current device date
txtDate=DateTime.Date(MyDate) 'This is current device date converted to a string that will eventually be strored in an SQL table as a separate field.
txtTime=DateTime.Time(MyDate) 'This is current device time converted to a string that will eventually be strored in an SQL table as a separate field.
Happy new year

I don't think your situation is different.
You just have to make the query with your variables.

B4X:
``````Dim oSql As SQL
Dim oRst As Cursor
Dim cQuery As String
Dim fDaysBetween As Double
Dim nNow As Long
Dim cDateNow As String
Dim cTimeNow As String
Dim txtDatepr As String
Dim txtTimepr As String
Dim cDateFormat As String
Dim cTimeFormat As String

'Make sure these are in the correct format
'if you use another format you must convert it first
txtDatepr = "2011-12-27"
txtTimepr = "16:35:16"
cDateFormat = DateTime.DateFormat
cTimeFormat = DateTime.TimeFormat
DateTime.DateFormat = "yyyy-MM-dd" 'set for sqlite
DateTime.TimeFormat = "HH:mm:ss" 'also for sqlite
nNow = DateTime.Now
cDateNow = DateTime.Date(nNow)
cTimeNow = DateTime.Time(nNow)

oSql.Initialize("","",False) 'Create dummy connection if you do not use sqlite
cQuery = "SELECT julianday('" & cDateNow & " " & cTimeNow & "') - julianday('" & txtDatepr & " " & txtTimepr & "') as DaysBetween"
oRst = oSql.ExecQuery(cQuery)
If oRst.RowCount > 0 Then
oRst.Position = 0
fDaysBetween = oRst.GetDouble("DaysBetween")
End If
DateTime.DateFormat = cDateFormat 'reset
DateTime.TimeFormat = cTimeFormat
cDateNow = DateTime.Date(nNow) 'back to your dateformat if you want to store it this way
cTimeNow = DateTime.Time(nNow)
End Sub``````

HTH, otherwise I do not understand what you need.

Stephen

#### Mahares

##### Expert
Longtime User
Stephen: Your solution is precisely what I was looking for. It is perfect. I had trouble in your initial code replacing the date and time with variables. The placement of single quotes and double quotes threw me out. But the way you parse them in your new code is perfect. I have been messing with this for 4 days non-stop.
Thank you very much for your help.

#### Smee

##### Well-Known Member
Longtime User
Couldnt you just use the Add ticks and dateparse ticks functions to convert and store the dates and then extract them as required?

Basic4android - Core

#### Mahares

##### Expert
Longtime User
I tried using the ticks, but the validity of the results for some reason varied from one hour of the day to the next. The days between dates is of course supposed to change as expected, but I am talking about giving bad math. In fact if you check my previous threads on this you will see that I tried the tick method. I found other code in the forum that uses the ticks. I tried it and got good results at times and conflicting results other times with the same code, which was puzzling. I can look for it and post it if you like. Stephen's method using the julianday within a query yielded the correct result every time I ran it. Thank you.

#### magarcan

##### Active Member
Longtime User
How can I calculate number of minutes between two Dates? I was thinking in using Julian Date, but I really think it's not the best way.

Thanks!!

#### Mahares

##### Expert
Longtime User
I tried several ways and ticks and only using the Julianday formula worked accurately. See code of 12/31/2011 in the same thread. Here is a sample:
oSql.Initialize("","",False) 'Create dummy connection if you do not use sqlitecQuery = "SELECT julianday('" & cDateNow & " " & cTimeNow & "') - julianday('" & txtDatepr & " " & txtTimepr & "') as DaysBetween"oRst = oSql.ExecQuery(cQuery)If oRst.RowCount > 0 Then oRst.Position = 0fDaysBetween = oRst.GetDouble("DaysBetween")

Then, to get the number of minutes, simply multiply fDaysBetween by 24 * 60. Then, can also round the result using: Round2(fdaysbetween,2) or what ever decimals you want.
I hope this helps.

Replies
2
Views
973
Replies
0
Views
2K
Replies
236
Views
29K
Replies
6
Views
2K
Replies
22
Views
19K