Time Conversion

I need to convert a time that is a string value to a time value only - i want to subtract 2 times to get the difference- it will not allow it because of it being a string?

Trying to accomplish this:

Currently the 15:10:00 and other time is a string.

15:10:00  -  15:01:00  =  9:00
srodgers45Asked:
Who is Participating?
 
mlmccConnect With a Mentor Commented:
Try

DTSToDateTime({FDC_Trips.atpsdate} &  " "  & {FDC_Trips.atpstime})

mlmcc

0
 
mlmccCommented:
Try this formula

mlmcc
Local StringVar strStart := {YourStartTimeSring};
Local StringVar strEnd := "{YourEndTimeSring};
local Timevar tmStart;
local Timevar tmEnd;
local NumberVar nbrSeconds;
local NumberVar nbrMinutes;
local NumberVar nbrHours;

tmStart := TimeValue(strStart);
tmEnd := TimeValue(strEnd);
nbrSeconds := DateDiff('s',DateTime(CUrrentDate,tmStart), DateTime(CurrentDate,tmEnd));
nbrHours := nbrSeconds\3600;
nbrSeconds := nbrSeconds mod 3600;
nbrMinutes := nbrSeconds\60;
nbrSeconds := nbrSeconds mod 60;

TimeValue(nbrHours,nbrMinutes,nbrSeconds);

Open in new window

0
 
srodgers45Author Commented:
Sorry i should have added that I have a date field that goes with the time - just separate
When I tried the formula above it comes up with all the times starting at 12:00. Do I need to add the date to the time?  When it tried before to do this formula it did not show properly

DTSToDateTime({FDC_Trips.atpsdate}+{FDC_Trips.atpstime})

The time shown on the left in the screen shot is the proper time. The time on the right side is when I use the formula above to convert to datetime value. No sure why it would be different?
error
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
mlmccCommented:
I should have said to display the answer in 24hour format so that 0 hours shows as 0 hours rather than 12

What does the date field have?

mlmcc
0
 
srodgers45Author Commented:
The format for the date is 01-01-2011
0
 
mlmccCommented:
Is the date field a date or string?

mlmcc
0
 
srodgers45Author Commented:
Sorry, a string
0
 
mlmccCommented:
If you need the date the function expects dates as 2011-01-01

mlmcc
0
 
srodgers45Author Commented:
That worked. Thanks for the expert help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.