Solved

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

Posted on 2008-06-23
5
490 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
[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
  • 3
  • 2
5 Comments
 
LVL 100

Expert Comment

by:mlmcc
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

by:samic400
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

by:samic400
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 100

Accepted Solution

by:
mlmcc earned 500 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

by:samic400
ID: 31469701
Thanks - exactly what I wanted.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

738 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