Converting masked input data to numeric field 2

[Steve-vfp9user]

I am trying to find a way to convert two fields so I can auto calculate the hours and minutes between the two.

I have some code that already calculates the time taken by adding several fields which I'm sure I can adapt.

LnH1=INT(Monday)+INT(Tuesday).....
LnM1=Monday+Tuesday.....
LnM=LnM1 % 0.6
LnH=LnH1+INT((LnM1-LnM)*5/3

REPLACE MYFIELD with LnH+LnM

This is ok because the field Monday, Tuesday etc are numeric 6,2 and the MYFIELD is also numeric 6,2

My problem is that in the app I'm working on has two fields timefrom and timeto both are Character 5 with an input mask !!:!!

What I'm trying to achieve is for example have:

Time from: 14:45
Time to: 16:30

Replace HOURSMINS with 1.45

HOURSMINS is a numeric 6,2 field

I would appreciate any pointers.


Thank you

Steve

 

[DSummZZZ]

If you MUST use those, convert them to numeric, reduce both to total seconds, add/subtract the seconds, then convert back to hours and minutes.


-Dave Summers-

Even more Fox stuff at:

http://www.dave-summers.net/foxprolinks.htm

 

[Olaf Doschke]

Even if you can't change a DBF, you can query into DTOT(Date())+hours*60*60+minutes*60 for display of the data in a grid or at least when computing the difference. (datetime2-datetime1)/(60*60) then is the difference in hours.

Edit: Of course hours = VAL(LEFT(chartime,2)) and minutes = VAL(RIGHT(chartime,2))

Bye, Olaf.

 

[Steve-vfp9user]

Hi Olaf,

Appreciate your post. I'll give this a go and post back.

Thank you

Steve

 

[Steve-vfp9user]

My apologies for the delay in posting back but I was determined to make this work one way or another albeit, slightly long winded (I'm sure the experts have a quicker way but hey!)

The issue here was I had two field that were CHARACTER C(5) TIMEFROM and TIMETO with a masked format !!:!!

I need to convert the time between the two and calculate it to hours and minutes and then replace a numeric field with the result. I had to incorporate a few "traps" to ensure there were no more that 59 minutes entered.

This is how I achieved it for anyone interested:

STORE "  " TO mhours
STORE "  " TO mmins
STORE SPACE(5) TO mtotalconv

IF VAL(LEFT(timefrom,2))>VAL(LEFT(timeto,2))
  WAIT "Invalid HOUR entered (Time from > Time to)" WINDOW NOWAIT
  REPLACE TIMEFROM WITH ""
  REPLACE TIMETO WITH ""
  RETURN 0
ENDIF

IF VAL(RIGHT(timefrom,2))>59 OR VAL(RIGHT(timeto,2))>59
  WAIT "Invalid MINUTE entered (MUST be <60" WINDOW NOWAIT
  REPLACE TIMEFROM WITH ""
  REPLACE TIMETO WITH ""
  RETURN 0
ENDIF

IF VAL(RIGHT(timeto,2))<VAL(RIGHT(timefrom,2))
  STORE VAL(LEFT(timeto,2))-VAL(LEFT(timefrom,2))-1 TO mhours
ELSE
  STORE VAL(LEFT(timeto,2))-VAL(LEFT(timefrom,2)) TO mhours
ENDIF

IF VAL(RIGHT(timeto,2))>VAL(RIGHT(timefrom,2))
  STORE VAL(RIGHT(timeto,2))-VAL(RIGHT(timefrom,2)) TO mmins
ELSE
  STORE VAL(RIGHT(timefrom,2))+VAL(RIGHT(timeto,2)) TO mmins
ENDIF

IF VAL(RIGHT(timefrom,2))>VAL(RIGHT(timeto,2))
  STORE VAL(RIGHT(timefrom,2))-VAL(RIGHT(timeto,2)) TO mmins	
ENDIF

IF mmins>59 OR mmins<0
  mmins=0
ENDIF

STORE STR(mhours,2)+"."+STR(mmins,2) TO mtotalconv

REPLACE HOURSMINST	WITH mtotalconv  &&  This is a character field
REPLACE HOURSMINS	WITH VAL(mtotalconv)  &&  This is the final numeric field

I appreciate the guidance and advice on this thread

Thank you

Steve

 

[Olaf Doschke]

Hi Steve,

Take a look at easier ways:

timefrom = "09:00"
timeto = "17:15"

Try
   t0 = Eval("{^2000-01-01 "+timefrom+":00}")
Catch
   WAIT "Invalid Time from entered" WINDOW NOWAIT
   Return 0
Endtry

Try
   t1 = Eval("{^2000-01-01 "+timeto+":00}")
Catch
   WAIT "Invalid Time to entered" WINDOW NOWAIT
   Return 0
Endtry

nDiff = t1-t0 && difference in seconds
If nDiff<0
   WAIT "Invalid times entered (Time from > Time to)" WINDOW NOWAIT
   Return 0
Endif

cDiff = Left(TTOC(DTOT(Date())+nDiff,2),5)
nDiff = nDiff/3600 && convert seconds into hours: 60*60 seconds/hour= 3600 seconds/hour
? nDiff, cDiff

Bye, Olaf.

 

[Steve-vfp9user]

Hey Olaf

As mentioned, I guessed you guys would have an easier way but it's a good learning experience!

Appreciate your time

Thank you

Steve

 

[Olaf Doschke]

Well, yes, but you don't need to go the hard way, I already said in august you can simply subtract two datetimes to get a difference. And converting hour and minute into a datetime just needs a base datetime you get from DTOT(DATE()). Or as I now did using a string representation {^YYYY-MM-DD hh:mm:ss} where you have YMD and s hardcoded and put in your hh:mm. This alone checks the validity of hh:mm, as the conversion will error otherwise.

It's essential you can easily work with dates and datetimes, in both directionns. You can also add seconds to a base datetime at some day midnight and such a base datetime is DTOT(DATE()), as converting any date simply adds midnight as time portion.

So in short:
datetime2-datetime1 gives Difference in seconds
date2-date1 gives Difference in days
date3+days dives the date in that distance
datetime3+seconds gives the datetime in that distance

To go forward months we have GOMONTH(), to go forward years use multiples of 12 months.

You can also disregard the whole section of date and datetime types and related operations and functions and do the straight forward math, but it's much easier not needing to care about days of a month etc. and let the system do things for you.

Bye, Olaf.