## Difference between two different date/time combinations

## Difference between two different date/time combinations

(OP)

Hi;

Can someone help me to write the following formula within Crystal Report. Here is the data I am working with and what I need:

There is a field called PSCrtDt (YYYYMMDD(number) and PSCrtTime (number) which I want to combine into one field called PSTiming. I then have a field called XHCrtDt (YYYYMMDD(number) and XHCrtTime (number) which I want to combine into one field called XHTiming. Then I want to know If PSTiming <= XHTiming then Yes else No

Appreciate any help I can get.

Thank you

Rose

Can someone help me to write the following formula within Crystal Report. Here is the data I am working with and what I need:

There is a field called PSCrtDt (YYYYMMDD(number) and PSCrtTime (number) which I want to combine into one field called PSTiming. I then have a field called XHCrtDt (YYYYMMDD(number) and XHCrtTime (number) which I want to combine into one field called XHTiming. Then I want to know If PSTiming <= XHTiming then Yes else No

Appreciate any help I can get.

Thank you

Rose

## RE: Difference between two different date/time combinations

20,160,515but it isn't clear just how the time is being stored. Can you advise whether my assumption of date is correct and if not provide some sample data and how that data should be interpreted. Can you also provide sample data for the time field please, and how the sample times should be interpreted.Once we understand the data it should be simple to achieve what you need.

Cheers

Pete

## RE: Difference between two different date/time combinations

So in order to calculate any date/time difference, you might need to categorize your date/time pairs, since those pairs missing either the subtrahend or minuend cannot be acted on, while those that can be subtracted, need to be converted to real date/time values before applying math. In my world, this was done by joining a Calendar table that had a yyyymmdd field associated with calendar date, manufacturing day, accounting day, YTD, MTD, etc.

BTW, after reading your question again, I see you only need a comparison. So each date and time text value would be concatenated to get a date/time text value while any date and time real values would be added to get a date/time real value to do math.

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Difference between two different date/time combinations

As long as the Dates are in the format of YYYYMMDD and the time fields are numeric (or if text are in the format HHMM or similar), the following formula should work:

## CODE

Hope this helps.

## RE: Difference between two different date/time combinations

First thank you both for responding. Excellent help and learned quite a few things.

pmax9999 I used your suggestion with a little modification for additional criteria and the formula now appears to be working. Never thought about only needing to test the time field if the date criteria was yes, learned something new and it helps with thinking through how to write criteria.

SkipVought and pman9999 I wanted to share with you what our file is showing for data and field definition so it will help me to better understand if the files are actually numeric:

field definition on the file is PScrtDt (Date Created):Number data displays as 20.160.620.00 and PSTimeCrt (Time Created): Number data shows as 145.915.00

I really appreciate the help.

Rose

## RE: Difference between two different date/time combinations

What you have is TEXT. 20160620 as a date representation, is not a value with which you can do any meaningful math. And I have no idea why your date would be represented as 20.160.620.00. But it surely cannot be used in any mathematical computation. Real dates, however, can and are used in math.

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Difference between two different date/time combinations

Thank you.

Would I be able to convert the text to a number if I had to with the data the way it is?

Thanks

Rose

## RE: Difference between two different date/time combinations

To convert ANY text representation of a date to a real date, you would need to parse appropriate portions and map those parsed portions representing year, month and day to a DateSerial function that would return a date serial value.

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Difference between two different date/time combinations

A simple test would be to try a mathematical operation on the date and time fields (eg,

{Table.PSCrtDT}/50). If it accepts the formula and performs the operation, the field is a number.Assuming the two fields are, in fact, numbers (as opposed to numeric text as suggested by Skip), the following conversion formulas will work:

1. Convert Date Field to True Date## CODE

2. Convert Time Field to True Time## CODE

3. Combine Date and Time fields to True Date/Time## CODE

Hope this helps.

Cheers

Pete

## RE: Difference between two different date/time combinations

I did exactly what you suggested and enter a formula (just to see what would happen) which was {XHCTRL00.XHCNTD}/50 and first crystal allowed me to save the formula and when I added this formula field to the report it actually showed results. So I guess this really is a numeric field with a odd set up. Thank you for suggesting the test and also thank you very much for the conversions as these will come in very handy going forward.

The report I was attempting to create looks great now and is providing all the information we need.

Thank you

Rose

## RE: Difference between two different date/time combinations

Pete