Link to home
Start Free TrialLog in
Avatar of Becky Edwards
Becky EdwardsFlag for United States of America

asked on

How do I combine a DATE Field with a TIME Field to form a DateTime field in Crystal Reports XI?

How do I combine a MULTIPLE DATE Field with a TIME Field to form a DateTime field in Crystal Reports XI?

My date field is broken up into year, month, day.  The data is the year field is 7 for 2007, 8 for 2008.  The day is 1,2,3,etc.  The month is 1,2,3,etc. Then I have a time field which is military time, in another field.  I need to be able to subtract DISCHARGE DATE (3 fields) and DISCHARGE TIME (1 military time field) from ADMIT DATE and ADMIT TIME (same format).  

I can do this in Crystal or Excel, I just need it!  Can anyone help!????
Avatar of Mike McCracken
Mike McCracken

You can create the datetime like this

Add a formula
DateTime(Date(2000+{YearField},MonthField,DayField,{TimeField})

If that doesn't work give an example of the fields

mlmcc
Avatar of Becky Edwards

ASKER

I tried your suggestion(see below). Perhaps I didn't quite understand. Error msg said "Wasn't a formula"
DateTime(Date(2000+{BADPLPP.LPADT3},{BADPLPP.LPADT1},{BADPLPP.LPADT2}{BADPLPP.LPLAT}))

Where LPADT3 is Year
LPADT1 is Month
LPATD2 is Day
LPLAT is Time

if you browse data in these fields you will find:
LPADT3 is 8
LPADT2 is 11
LPADT3 is 14
LPLAT is 2359 (the time in military hours)
if i were to make theDate ToText it would look like this "11/14/2008" but I would have to format it like so:
ToText ({BADPLPP.LPADT1},'00')&'/'&ToText({BADPLPP.LPADT2},"00")&'/'&ToText({BADPLPP.LPADT3},"00")
Please Help!!!!!  That particular one did not work, so I am still up in the air on this one.  Thank you.
You'll have to do it this way

DateTime(Date(2000+{BADPLPP.LPADT3},{BADPLPP.LPADT1},{BADPLPP.LPADT2}),
                Time({BADPLPP.LPLAT})\100,{BADPLPP.LPLAT}) MOD 100,00) )

mlmcc
It says "The remaining text does not appear to be part of the formula" and ,00)) is highlighted.
DateTime(Date(2000+{BADPLPP.LPADT3},{BADPLPP.LPADT1},{BADPLPP.LPADT2}),Time({BADPLPP.LPLAT})\100,{BADPLPP.LPLAT})MOD 100,00))

ASKER CERTIFIED SOLUTION
Avatar of Mike McCracken
Mike McCracken

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you So MUCH!  It worked.