[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 947
  • Last Modified:

Datediff functon calculate quarter to the hour

How can I get the DateDiff function to calculate to the quarter of the hour, as in 2.50 (i think that is how it should be displayed), in the example below:

DateDiff("h", "03/09/2010 7:00 AM", "03/09/2010 9:30 AM")

I tried "q" as a parameter and that didn't work.
0
michael1174
Asked:
michael1174
  • 4
  • 4
  • 4
  • +3
3 Solutions
 
Rey Obrero (Capricorn1)Commented:
try

DateDiff("s", "03/09/2010 7:00 AM", "03/09/2010 9:30 AM")/3600
0
 
Patrick MatthewsCommented:
If what you really mean is, "I need the difference between two datetimes, measured in quarter hours", then stay away from DateDiff, which does not measure elapsed time at all, but rather it measures the number of time interval boundaries occurring between two datetimes.

Try this:

SELECT Datetime1, Datetime2, Int((Datetime2 - Datetime1) *24) + Switch(((Datetime2 - Datetime1) *24) - Int((Datetime2 - Datetime1) *24)) >= 0.75, 0.75, ((Datetime2 - Datetime1) *24) - Int((Datetime2 - Datetime1) *24)) >= 0.5, 0.5, ((Datetime2 - Datetime1) *24) - Int((Datetime2 - Datetime1) *24)) >= 0.25, 0.25, True, 0)
FROM SomeTable
0
 
Patrick MatthewsCommented:
The formula I gave will assign 0-24 minutes to the "zero" quarter hour, 25-49 minutes to the "15" quarter hour, etc.

For rounding, we can adapt cap1's formula:

SELECT Datetime1, Datetime2, Round(4 * DateDiff("s", Datetime1, Datetime2) / 3600, 0) / 4 AS RoundedHrs
FROM SomeTable
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Richard DanekeTrainerCommented:
I can get to hours and minutes with Format(DateTime2, DateTime1, "hh.nn")
0
 
Richard DanekeTrainerCommented:
in excel, this is neat:  =CONVERT(DateTime2 - DateTime1 ,"day","hr") does the trick providing decimal calculation as formatted
0
 
GRayLCommented:
DoDahD:  Type mismatch error.
0
 
Richard DanekeTrainerCommented:
OOPS;  Format(DateTime2 - DateTime1, "hh.nn")
Since dates are already numbers, the difference is a dateserial
0
 
Richard DanekeTrainerCommented:
You can convert the .nn minutes to decimal by multiplying it tiems 1.6667
0
 
GRayLCommented:
michael1174:  As I understand the question, what is the number of quarter hours between two datetimes where each datetime is first rounded to the nearest quarter hour?  If not so, please clarify.
0
 
GRayLCommented:
That should be:  As I understand the question, what is the number of hours between two datetimes where each datetime is first rounded to the nearest quarter hour?  If not so, please clarify.
0
 
GRayLCommented:
It is to say the decimal part of the answer will be to the quarter hour - 0.0, 0.25, 0.5. 0.75
0
 
Gustav BrockCIOCommented:
If you wish to count the difference the same way as for hours and a quarter is either the first, second, third or fourth part of an hour of the day, which closely mimics the behaviour of DateDiff, you'll have to adjust the time as shown in the function below.

To obtain a "decimal" hour, just divide the output by 4.

/gustav
Public Function DateDiffQuarterHour( _
  ByVal datFrom As Date, _
  ByVal datTo As Date) _
  As Long
  
' Returns difference between datFrom and datTo in quarters of the hour.
' 2007-01-07. Cactus Data ApS, CPH.

  Const cintMinutesQuarter  As Integer = 15
  
  Dim datDateFrom       As Date
  Dim datDateTo         As Date
  Dim datTimeFrom       As Date
  Dim datTimeTo         As Date
  Dim lngQuarterHour    As Long
  
  datDateFrom = DateSerial(Year(datFrom), Month(datFrom), Day(datFrom))
  datDateTo = DateSerial(Year(datTo), Month(datTo), Day(datTo))
  datTimeFrom = TimeSerial(Hour(datFrom), Int(Minute(datFrom) / cintMinutesQuarter) * cintMinutesQuarter, 0)
  datTimeTo = TimeSerial(Hour(datTo), Int(Minute(datTo) / cintMinutesQuarter) * cintMinutesQuarter, 0)
  datFrom = datDateFrom + datTimeFrom
  datTo = datDateTo + datTimeTo
  
  lngQuarterHour = DateDiff("n", datFrom, datTo) \ cintMinutesQuarter
  
  DateDiffQuarterHour = lngQuarterHour
    
End Function

Open in new window

0
 
michael1174Author Commented:
cactus_data, I had to change your return type as a double because I wanted to return the decimal.. i had also divided by 4 like you suggested and your function worked.

GRayL, yes, i am trying to get the quarter of the hour...0.0, 0.25, 0.5. 0.75

capricorn1: thanks, it works.

matthewspatrick: thanks, for the rounding portion of capricorn1 forumula... since i am not using sql to get the result, i am not going to implement your first suggestion, but thanks.

Both capricorn1 and cactus_data forumla seems to work for me, so i guess it just comes down to which one i like...
0
 
Gustav BrockCIOCommented:
No, you have to decide for what you wish to achieve.

It is either a time span counted in decimal hours rounded to 0.25 (or not?) - or it is a difference in quarters of the hour exactly as you, for example, have one hour difference between #08:50# and #09:10#. This is what my function does.

Also, the return data type of my function should not be changed as it returns integers only.
Your data type for a converted value to a decimal count of hours must, of course, be different to be able to hold fractions:

dblHours = DateDiffQuarterHour(datFrom, datTo) / 4

Note that this will - per definition by dividing an integer by 4 - be rounded to 0.25.

/gustav
0
 
Gustav BrockCIOCommented:
Here is how to calculate the timespan in quarter hours as a decimal hour value.

To obtain the result rounded correctly up/down (at 5/5) to integer quarters, you can use this expression:

dblHours = Int(Format(QuarterHours(datFrom, datTo) * 4, "0")) / 4

The trick is that Format rounds correctly for any value - here to an integer.

/gustav
Public Function QuarterHours( _
  ByVal datFrom As Date, _
  ByVal datTo As Date) _
  As Double

' Returns difference between datFrom and datTo in decimal quarters hours.
' 2007-04-13. Cactus Data ApS, CPH.
  
  Const clngSecondsQuarter  As Long = 15 * 60
  
  Dim lngSeconds      As Long
  Dim dblQuarterHours As Double
  
  lngSeconds = DateDiff("s", datFrom, datTo)
  dblQuarterHours = lngSeconds / clngSecondsQuarter
  
  QuarterHours = dblQuarterHours
  
End Function

Open in new window

0
 
michael1174Author Commented:
Thanks!
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 4
  • 4
  • 4
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now