Solved

Need help with crystal reports xi formula - convert time from military to regular time

Posted on 2008-06-23
5
486 Views
Last Modified: 2011-08-18
I have two fields - that are both numeric - one or appointment start time and one for appointment end time. They are both stored in a numeric field in SQL Server.

What I need to do, in CR XI, is read the following and I have no idea how to create the formula:
1) read in the number - ex. 1315
2) convert it to a string to manipulate it
3) determine the length of the string - if the length is 3, then I only need to go after the first position of the string because it is a single digit - for example - 815 as opposed to 1730
4) if the first two bytes are greater than 12, I need to subtract 12 - in my case of 13, I need to convert to 1, something like 17 would be a 5
5) then I need to put the first two positions together then concatentate with ":" and then the last two positions -

So if my value is 1315 - I want it to read 1:15 PM

Could someone put together a snippet of code to show me how to do this.

It would be a huge help!!!

Thanks in advance.
0
Comment
Question by:samic400
  • 3
  • 2
5 Comments
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Try this formula

Time(1315\100,1315 mod 100,0)

If this is a field then

Time({YourTimeField} \ 100, {YourTimeField} mod 100,0)

mlmcc
0
 
LVL 13

Author Comment

by:samic400
Comment Utility
How would I get a ":" in between the two sections of hours and minutes? So it is 1:15? Does time do that?
0
 
LVL 13

Author Comment

by:samic400
Comment Utility
I see now after doing it. Is there anyway I can trim it down from say 10:45:00 AM to like 10:45 AM so I am ignoring the seconds?
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
Comment Utility
Sure.
CHange the formula to
ToText(Time({YourTimeField} \ 100, {YourTimeField} mod 100,0),'h:mm t)

Another way would be to simply format the field
Right click the field
CLick FORMAT
Choose the format you want.

mlmcc
0
 
LVL 13

Author Closing Comment

by:samic400
Comment Utility
Thanks - exactly what I wanted.
0

Featured Post

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

Join & Write a Comment

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

728 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

9 Experts available now in Live!

Get 1:1 Help Now