Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

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

Posted on 2008-06-23
Medium Priority
494 Views
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!!!

0
Question by:samic400
[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
• 3
• 2

LVL 101

Expert Comment

ID: 21847832
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

ID: 21848101
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

ID: 21848209
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 101

Accepted Solution

mlmcc earned 2000 total points
ID: 21848524
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

ID: 31469701
Thanks - exactly what I wanted.
0

## Featured Post

Question has a verified solution.

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

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
###### Suggested Courses
Course of the Month4 days, 17 hours left to enroll