Solved

diff b/w CurrentTime and Timestamp field

Posted on 2014-12-04
9
222 Views
Last Modified: 2014-12-08
How do I write a formula field that calculates the difference between the CurrentTime and my database field {proposal.timestamp}?

I tried datediff function, but that did not work, it did not like me using the crystal function "CurrentTime".
0
Comment
Question by:IO_Dork
  • 3
  • 3
  • 3
9 Comments
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 250 total points
ID: 40481945
WHat data is in TimeStamp?

What type is it?

I suspect it may be an integer showing milliseconds rather than a time.

It may also be a datetime type with the date being 1 Jan 1900.

Normally you would use

DateDiff('s',{proposal.timestamp},CurrentTime())

You might try

DateDiff('s',{proposal.timestamp},CurrentDateTime())

mlmcc
0
 
LVL 34

Assisted Solution

by:James0628
James0628 earned 250 total points
ID: 40482576
CurrentTime won't work, because that is _only_ the time.  DateDiff requires date or datetime arguments (which would explain why it's called DateDiff and not TimeDiff ;-).  Maybe you thought/assumed that CurrentTime included the date.

 If you want the current date and time, use CurrentDateTime.  Assuming that your field is a datetime, it'd be something like mlmcc's last suggestion, although you don't need the () after CurrentDateTime.

 If you actually want to compare just the times and ignore the dates, the simplest thing might be to combine each time with the current date, and then use DateDiff on those datetimes.

 James
0
 

Author Comment

by:IO_Dork
ID: 40483434
my timestamp includes date and time, so I should be able to use datediff with currentdatetime.  I'll try that.
0
 

Author Comment

by:IO_Dork
ID: 40483495
ok, that worked, but how to I get the datediff result to show as hours:minutes instead of just a number that represents total minutes?

for example, it is showing a record with a diff of 132 minutes, I want it to display as 2:12.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 250 total points
ID: 40483923
You will have to convert it yourself.

Try this formula

Local NumberVar MinuteDiff;
Local NumberVar HoursDiff;

MinuteDiff := DateDiff("m",{proposal.timestamp},CurrentDateTime);
HoursDiff := MinutesDiff \ 60;
MinutesDiff := MinutesDiff MOD 60;

CStr(HoursDiff,0,'') & ':' & CStr(MinutesDiff,'00')

Open in new window


mlmcc
0
 
LVL 34

Accepted Solution

by:
James0628 earned 250 total points
ID: 40483929
I assume that the difference will never be negative.

 Will the difference ever be more than 24 hours?  If so, how do you want to display those values?

 If the difference will always be less than 24 hours, you could use something like this:

Time ((DateDiff ('n', {proposal.timestamp}, CurrentDateTime) / 60) / 24)

 The Time function can be given a value that's expressed in 24 hour units, so that formula divides the minutes by 60 to convert it to hours, and then divides that by 24 to get "24 hour units", which are then converted into a time.  You could also just divide the minutes by 1440 (24 * 60).  You can format the time however you like (probably in a 24-hour, or "military time", format).  But if the value is more than 24 hours, the time just wraps around (eg. 25 hours will be 1 AM), so you'd have to decide how you wanted to display those values.

 James

 PS: mlmcc hadn't posted that formula when I started working on my post.  Of course you could use a formula like that.  I just decided to try to come up with something "simpler" (it's only one line), although it does have its limits.  But the interval code in mlmcc's formula should be "n", not "m" (which is months).
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 40484277
Thanks James.  Too much in other reporting tools where m is minutes and M is months

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
ID: 40484479
No problem.  It's easy to forget.

 James
0
 

Author Comment

by:IO_Dork
ID: 40484779
Thanks. I'll try this out when I am back at work Monday.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

708 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now