By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,441 Members | 996 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,441 IT Pros & Developers. It's quick & easy.

How to calculate time difference between datetime and only time

P: 1
Hi sir, I want to calculate time in a proper format (hours, minute and second).
My first field type is datetime directly fetch from database and second one is fixed it's 08:30:00.
Feb 11 '17 #1
Share this Question
Share on Google+
1 Reply


P: 1
Hi mayurm38.

A date/time/datetime is a fractional number.
The whole number part is the day number where jan 1 1900 is day number 1.
The fractional part is the time. Since a day has 24 hours the value of 1 hour is 1/24 or 0.0416666667. 6 hours is 0.25.

So in order to calculate the time difference only, we have to get rid of the date part.

The next piece of code is one way of doing that.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Sub timeDifference()
  5.    Dim dt   As Date  'date and time
  6.    Dim tm   As Date  'time only
  7.    Dim dif  As Double
  8.  
  9.    dt = #2/20/2017 2:30:00 PM#
  10.    tm = #8:30:00 AM#
  11.  
  12.    dif = dt - Int(dt) - tm 'remove the date(whole number) part from dt
  13.  
  14.    'next line prints
  15.    ' 0,249999999997575          06:00:00
  16.  
  17.    Debug.Print dif, Format(dif, "hh:mm:ss")
  18. End Sub
  19.  
Feb 22 '17 #2

Post your reply

Sign in to post your reply or Sign up for a free account.