How To Calculate the Number of Days Between Two Dates

[Mahares]

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.

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]

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:

DateTime.DateParse("12/24/2011") + DateTime.TimeParse("6:29:17 PM")

Try this code:

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]

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:

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:

 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]

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]

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]

Try to run my code exactly as it is. If it doesn't work please post the values that you assigned.

 

[Mahares]

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]

This code is simpler and allows you to set the time zone:

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]

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.
Thank you for your assistance.

 

[Smee]

my 2 cents fwiw

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

   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

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]

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]

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:

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]

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]

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

queryTime=txtDate & " " & txtTime

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

fDaysBetween=Round2(fDaysBetween,2)

Hope that helps,

David

 

[bees]

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.

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]

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]

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]

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]

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]

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.