Change military time to standard time 2

[cutpen]

How can I change military time which is in a field, e.g. 1800 to standard time e.g. 6:00pm?

 

[turtledisc]

Is the field itself a datetime or time field?

If so, you can change the format by right-clicking on the field and selecting "Format Field". Click on the "Time" tab and select the appropriate option from the list displayed.

Jacquie Kelly

 

[cutpen]

It is a number field. If I put Time in front of the field it shows as 12:00am for any number that is in that field

 

[cutpen]

Also, I am using 8.5 and on the format field there is not a time tab

 

[turtledisc]

How are the times displayed? Does it show 1845 for 6:45pm? Could you post some sample data and what version of crystal reports you're running?

Jacquie Kelly

 

[rosemaryl]

Dear Cutpen,

If the field is a number field the following will work:

umberVar T := 1845; //change to your field
NumberVar H := tonumber(left(totext(t),2));
NumberVar M := tonumber(totext(t) [3 to 4]);
time(h,m,0)

//field will be returned as time. After placing on report
//right click and choose format field and select the format
//you want.

If the field is a string, then the following will work:
StringVar ST := '1845'; //change to your field
NumberVar H := tonumber(left(ST,2));
NumberVar M := tonumber(ST [3 to 4]);
time(h,m,0)

//field will be returned as time. After placing on report
//right click and choose format field and select the format
//you want.

I am sure there is probably an easier way, but that works.

Best,
ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[cutpen]

Yes, I am using 8.5. Notice that field of start time that has 1500 (Number) I wrote a formula time(field) that shows as 12:00AM.

e.g. start time @time
1500 12:00AM

 

[cutpen]

That makes 1500 go to 1:50am instead of 6:00PM

 

[rosemaryl]

Dear Cutpen,

I have tested both my formulas with strings and with various numbers and 1500 will return 3:00 which is correct.

Perhaps you weren't responding to me?

Regards,
ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[cutpen]

I'm sorry, maybe I don't understand. Will you check my formula?

NumberVar T := {HMN_UNSHDDAY.S_START};
NumberVar H := tonumber(left(totext(t),2));
NumberVar M := tonumber(totext(t) [3 to 4]);
time(h,m,0)

 

[rosemaryl]

Dear Cutpen,

Thankyou! My mistake was only testing 4 digit numbers. I believe that the following corrected formula will work for you.

NumberVar T := {HMN_UNSHDDAY.S_START};
numbervar l := len(totext(tonumber(t)));
numbervar c := select(l)
case 0 : 0
case 1 : 0
case 2 : 0
case 3 : 1
case 4 : 2;
NumberVar H := tonumber(left(totext(t),c));
NumberVar M := tonumber(right(totext(t),2));
time(h,m,0)

Basically, we don't select the first 2 characters, it depends upon the length of the converted string. Will you test the modified formula and let me know if it works for you?

Best regards,
ro


Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[cutpen]

Sorry, but I am getting an error message saying "the remaining text does not appear to be part of the formula" inbetween the bottom two lines. Is this in crystal syntax?

case 4 : 2;
NumberVar H := tonumber(left(totext(t),c));

 

[rosemaryl]

Dear Cutpen,

Yes this is all crystal syntax.

Did you copy my formula? Or did you type it? Can you paste in exactly what you have in the formula?

Regards,
ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[cutpen]

I am sorry to be so much trouble. Here is what I have:

NumberVar T := {HMN_UNSHDDAY.S_START};
numbervar l := len(totext(tonumber(t)));
numbervar c := select(l)
case 0 : 0
case 1 : 0
case 2 : 0
case 3 : 1
case 4 : 2;
NumberVar H := tonumber(left(totext(t),c));
NumberVar M := tonumber(right(totext(t),2));
time(h,m,0)

 

[lbass]

Ro,

I think you will remember this, since I think I first learned this from you. In 8.0 and 8.5, you need an extra semi-colon after the last case statement--for no rhyme or reason. If you are using select case in a series of clauses connected by "and" (instead of in a variable as above), you need an extra close paren.

So the formula should say:

case 4 : 2;;

-LB

 

[rosemaryl]

Dear Lbass,

Oh you are right! I am testing in 10 and it had slipped my mind about the issue in 8.5. You have a good memory as it was me that told you.

Put the extra semicolon in cutepen and it will be fixed.

Regards,
ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[rosemaryl]

Dear Cutpen,

Just so that you have the entire formula correct, copy this:

NumberVar T := {HMN_UNSHDDAY.S_START};
numbervar l := len(totext(tonumber(t)));
numbervar c := select(l)
case 0 : 0
case 1 : 0
case 2 : 0
case 3 : 1
case 4 : 2;;
NumberVar H := tonumber(left(totext(t),c));
NumberVar M := tonumber(right(totext(t),2));
time(h,m,0)

//note the extra semicolorn on the case 4 : 2;; line.

Best,
ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[cutpen]

now when I run the report, it says "this string is non-numeric" What could this mean since the field says that it is a number?

 

[rosemaryl]

Dear Cutpen,

Ok. That can happen if the field is blank or null. Give me a few minutes to further tinker with the formula.

Regards,
ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[rosemaryl]

Dear Cutpen,

Here is a new and much simpler formula. The problem I was having (besides driving down the rosy path) was that when the number was transformed to text it wasn't keeping the padded 0s. Now it does. Now this formula works as follows:

If any number is entered that is less than a 4 digit string then it will padd with 0s to the left to make it a 4 digit string. So 21 would be perceived as 0021 and not 2100.

NumberVar T := {HMN_UNSHDDAY.S_START};

T := if isnull({HMN_UNSHDDAY.S_START})
then 0 else T;

stringvar n := totext(T,'0000');
//the format string '0000' is required!

numbervar Hs := ToNumber(left(n,2));
numbervar Ms := toNumber(right(n,2));

time(hs,ms,0)
//formula end
//don't forget to format the field for the time display
//format that you want

Please keep in mind that if the field is null I am setting it to 0 which will return midnight. If this is a possibility you will want format the field to suppress if the field is null so that it doesn't display.

Best regards,
ro


Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762