Forumla that will subract days and hours.

[mudflap]

Ok, I have a couple of fields.

Creation date ,Creation time ,Preflight Date, Preflight Time

What I want to do is find out how long between the
Creation date to the preflight date is.
But I need to work in the time also because some have a diffrence of 0 days so I need to use the time instead.

Any ideas.

Peter

 

[rosemaryl]

Dear mudflap:

Here you go:

//begin formula
WhilePrintingRecords;

NumberVar TotalSec := datediff("s",({CreationDate} + {CreationTime}),({PreflightDate} + {PreflightTime})) ;

NumberVar Days := Truncate (TotalSec / 86400);
NumberVar Hours := Truncate (Remainder ( TotalSec,86400) / 3600);
NumberVar Minutes := Truncate (Remainder ( TotalSec,3600) / 60);
NumberVar Seconds := Remainder ( TotalSec , 60);

Totext ( Days, '00', 0,'') & ':'&
Totext ( Hours, '00', 0,'') & ':'&
Totext ( Minutes,'00', 0,'') & ':'&
Totext ( Seconds,'00', 0,'')

//end formula

Hope that helps you,

ro
Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[mudflap]

My time fields seem to be strings...Does this mess things up?

Thanks
Peter

 

[Naith]

Convert it using CTime.

 

[mudflap]

How? Remeber...Never even read a book about this stuff.

Thanks
{eter

 

[rosemaryl]

Dear Mudflap,

Sorry for the delay - I was out sick yesterday with a nasty stomach bug!

What Naith is indicating is that you convert your string to time using ctime as in

Ctime(mytimestringfield)

Try that, does it work?

ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[mudflap]

No, gives me an remaining text does not appear to be part of the formula.

Peter

 

[synapsevampire]

Post what is contained within this field, you're probably going to have to parse it into a proper format first.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[mudflap]

Contained is a time in a 12 hour clock format.

I did this

ctime({preflight time})

 

[rosemaryl]

Dear Mudflap,

Just so that we are all on the same page, can you post an example "exactly" as it looks?

ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[mudflap]

WhilePrintingRecords;

NumberVar TotalSec := datediff("s",({CreationDate} + {CreationTime}),({PreflightDate} + {PreflightTime})) ;

NumberVar Days := Truncate (TotalSec / 86400);
NumberVar Hours := Truncate (Remainder ( TotalSec,86400) / 3600);
NumberVar Minutes := Truncate (Remainder ( TotalSec,3600) / 60);
NumberVar Seconds := Remainder ( TotalSec , 60);

Totext ( Days, '00', 0,'') & ':'&
Totext ( Hours, '00', 0,'') & ':'&
Totext ( Minutes,'00', 0,'') & ':'&
Totext ( Seconds,'00', 0,'')

This gives errors because the time field is a string.
Where do I put this ctime formula????

 

[synapsevampire]

You need to convert the time format into a standard time format for Crystal:

Here's one way of setting up the ctime():

CTime (18, 30, 30)

Stating that your data is in 12 hour clock format doesn't help much, PLEASE post an example, but this may help:

If the format is "10:30:15 AM" or "10:30:15 AM", then the ctime() would work against it.

So if it contains "10/30/15 AM"

Use:

ctime(Replace({MyTable.MyTime}, "/", ":&quot)

Meaning that we're replacing the text "/" with ":".

The point being that you have a proprietary time format, so you'll need to convert it into something Crystal can understand.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[mudflap]

Currently my time looks like this
10:00:00
14:00:00

How would I take that is make it a none string.

 

[rosemaryl]

Dear Mudflap,

Try this:

WhilePrintingRecords;

NumberVar TotalSec := datediff("s",({CreationDate} + Ctime({CreationTime})),({PreflightDate} + Ctime({PreflightTime}))) ;

NumberVar Days := Truncate (TotalSec / 86400);
NumberVar Hours := Truncate (Remainder ( TotalSec,86400) / 3600);
NumberVar Minutes := Truncate (Remainder ( TotalSec,3600) / 60);
NumberVar Seconds := Remainder ( TotalSec , 60);

Totext ( Days, '00', 0,'') & ':'&
Totext ( Hours, '00', 0,'') & ':'&
Totext ( Minutes,'00', 0,'') & ':'&
Totext ( Seconds,'00', 0,'')


---
If it is a text field, that will work if it is formatted as you indicated.

To test this for yourself. Do this:

Formula

Ctime("14:00:00)

This should return 2:00 p.m.

By the way, you value indicates a 24HR clock, military time.

Hope this helps,

ro
Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[mudflap]

WhilePrintingRecords;

NumberVar TotalSec := datediff("s",({Prepress WIP.Creation Date} + Ctime({Prepress WIP.Creation Time})),({Prepress WIP.Preflight (Date) (User Defined)} + Ctime({Prepress WIP.Preflight (Time) (User Defined)}))) ;

NumberVar Days := Truncate (TotalSec / 86400);
NumberVar Hours := Truncate (Remainder ( TotalSec,86400) / 3600);
NumberVar Minutes := Truncate (Remainder ( TotalSec,3600) / 60);
NumberVar Seconds := Remainder ( TotalSec , 60);

Totext ( Days, '00', 0,'') & ':'&
Totext ( Hours, '00', 0,'') & ':'&
Totext ( Minutes,'00', 0,'') & ':'&
Totext ( Seconds,'00', 0,'')


Gives me a Error " A number or currency amount or boolean string is expected here"
Puts the cursor in front of datediff.

 

[rosemaryl]

Dear Mudflap,

This formula works fine for me, I get no errors.

I think the problem is your time fields.

Let's try this. Instead of converting your date/time in the formula, let's create a new formula to do that one, for each date time.

//Formula 1 - Created
{Prepress WIP.Creation Date} + Ctime({Prepress WIP.Creation Time})


//Formula 2 - Preflight

{Prepress WIP.Preflight (Date) (User Defined)}
+ Ctime({Prepress WIP.Preflight (Time) (User Defined)})

Now, can you create each of those formulas without errors?

If not, test your string with the following:

//make it time

stringvar my;

my := replace({Prepress WIP.Preflight (Time) (User Defined)},':',',');

time(my)

What was the result?

Once we can get those fields recognized as date-time fields - then the formula should work no problem.

ro

does that make it a time field?

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[synapsevampire]

Nice step by step approach, Ro.

Have you confirmed that all of the date fields are truly dates and not strings too?

mudflap: You can right click the table in the field explorer and select show field type.

This will allow you to quickly identify field types in your listing. Dates should always be of type date or datetime. It may be that your date is a string, or it's already a datetime, in which case Ro's efforts have been wasted.

Post the date types and a sample of the data for both dates.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[rosemaryl]

Dear SynapseVampire,

Thanks, -k.

You are right, it would be very helpful here to know exactly what the field types are.

Mudflap, what's up?

ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[creyes]

I have been following this because I’m having the same problem:
I have a date/time field (time is always 12:00:00) that I have converted to Date by using Cdate. My time field is a 4-digit string (i.e. 1234).
I want to convert it to a military time field, without seconds, and find out the time difference between Dispatched Date and Time and Cleared Date and Time. (Especially when it runs past midnight).

I have used the following to convert to time:
ctime({MP_INMAST_VIEW.DI_TIME_G}) – I get an error message that it is a “bad time format string” when I try to put it in the report.

So then I tried:
ctime(tonumber({MP_INMAST_VIEW.DI_TIME_G})) – It turns into a time but they are all “12:00:00”

I’m stuck.