?
Solved

Subtracting 2 date fields into days, hours and minutes

Posted on 2003-02-25
8
Medium Priority
?
1,278 Views
Last Modified: 2008-02-01
I am trying to subtract two DateTime fields with the results showing how many days, hours and minutes are between the two dates. For example,

02/24/03 3:10:03 - 02/23/03 9:05:45 =

1 Day 6 hours 5 min [if I am subtracting right]

ToText(DATEDIFF('d',{TASKS.OPENDATE}, {TASKSACT.REQUESTED}))+ ' Days '
+ Totext(DatePart('h',(DateTimeValue ({TASKS.OPENDATE}-{TASKSACT.REQUESTED})) + ' Hours '))
+ Totext(DatePart('m',(DateTimeValue ({TASKS.OPENDATE}-{TASKSACT.REQUESTED})) + ' Min '))

Crystal keeps giving me an error that "A number is required here." by the ' Days ' portion, line 2 of formula. Can someone help?

Thanks, Laura
0
Comment
Question by:LRadon
[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
  • 5
  • 3
8 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 8020743
You have )'s in the wrong place

Try this

ToText(DATEDIFF('d',{TASKS.OPENDATE}, {TASKSACT.REQUESTED}))+ ' Days '
+ Totext(DatePart('h',(DateTimeValue ({TASKS.OPENDATE}-{TASKSACT.REQUESTED})))) + ' Hours '
+ Totext(DatePart('m',(DateTimeValue ({TASKS.OPENDATE}-{TASKSACT.REQUESTED})))) + ' Min '

mlmcc
0
 

Author Comment

by:LRadon
ID: 8020963
Thanks, mlmcc. I'm new to this site and am not sure exactly how the points work. I only have 75.

Your corrections spit out a reponse and the number of days is correct, however, I don't think the hour and minutes portion is calculating correctly. Here is an example:

02/03/03 11:45:24AM  02/03/03 11:46:32AM and the result was
0 Days 23.00 Hours 12.00 Min. [I don't need seconds.]

The # of days is correct, but, shouldn't the hours be 0.00 and the minutes be 1.00?

Thanks, Laura
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 8023199
Yes they should.  I assume the subtraction was in the correct order (Larger - smaller)

I don't have resources here to test with so I'll look into it in the morning.

By the way what version of CR?  Are you using Crystal or Basic syntax for the formula?

mlmcc
0
Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

 
LVL 101

Expert Comment

by:mlmcc
ID: 8027563
Try this

ToText(DATEDIFF('d',{TASKS.OPENDATE}, {TASKSACT.REQUESTED}))+ ' Days '
+ ToText(DATEDIFF('h',{TASKS.OPENDATE}, {TASKSACT.REQUESTED})) mod 24 +  ' Hours '
+ ToText(DATEDIFF('n',{TASKS.OPENDATE}, {TASKSACT.REQUESTED})) mod 60 +   ' Min '

in date time calculations m is for month n is for minutes

Datediff wil give you the number of PERIODS between the dates.  Using the modulus (MOD) function eliminates the full next higher period.  To get hours you must eliminate the full days

mlmcc
0
 

Author Comment

by:LRadon
ID: 8107231
Hi,

It finally took me a while to get back to this. I used the code directly above this comment and I am getting an error that says that a nummber or currency amount is required here and the cursor is always right before the "ToText" function. I played around with the formula and I deleted the "mod 24/60" parts and I didn't get that error, but now the hours and minutes are in the hundreds.

I am making progress and I bought more points. Please help me solve this.

Thanks, Laura
0
 
LVL 101

Accepted Solution

by:
mlmcc earned 500 total points
ID: 8107373
Sorry missed some ( )

ToText(DATEDIFF('d',{TASKS.OPENDATE}, {TASKSACT.REQUESTED}))+ ' Days '
+ ToText((DATEDIFF('h',{TASKS.OPENDATE}, {TASKSACT.REQUESTED})) mod 24) +  ' Hours '
+ ToText((DATEDIFF('n',{TASKS.OPENDATE}, {TASKSACT.REQUESTED})) mod 60) +   ' Min '

mlmcc
0
 

Author Comment

by:LRadon
ID: 8107465
I am being fair with this grade. I finally got an answer within a half hour of posting my finally comment. Those () did the trick!!!!!!!!!!!!!!!!!Thanks, Mlmcc.

My boss was very pleased that it only cost $12.95 to solve this dilemma.


From Laura

THANK YOU!! THANK YOU!! THANK YOU!! THANK YOU!! THANK YOU!! THANK YOU!! THANK YOU!! THANK YOU!! THANK YOU!! THANK YOU!! THANK YOU!! THANK YOU!! THANK YOU!! THANK YOU!! THANK YOU!! THANK YOU!!
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 8108501
No problem.  Should have seen that sooner.

Glad I could help

mlmcc
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month13 days, 19 hours left to enroll

800 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