substract time in foxpro

[namax]

I have a sign in and sign out records sheet which I would like subtract to find out how long my staff worked each day.

The sign in time is 7:45 am and sign out time is 4:06 pm (which is exactly 8 hours and 21 minutes).However,people sign in and sign out in different times.Some sample data are as follows;7:30am-3:00pm,8:00am-4:00pm,8:30am-4:15pm,etc...

The data type I used is in character.

Will anybody help me with some codes that would generate results that I would use to find out how much time people worked, either extra or less each day.

Thank you in advance for your help.

 

[bborissov]

Better use DateTime field instead of char, but:

T1 = "07:30 AM"
T2 = "04:30 PM"
? (CTOT(t2)-CTOT(t1)) && Time difference in seconds

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[Mike Lewis]

Just to add to Borislav's answer ...

If you're holding each pair of times in a single string (e.g. "7:30am-3:00pm"), you will first need to parse out the start and end times:

lcTimes = "7:30am-3:00pm"
lcStart = GETWORDNUM(lcTimes, 1, "-")
lcEnd = GETWORDNUM(lcTimes, 2, "-")
lnSecondsWorked = CTOT(lcEnd) - CTOT(lcStart)

That will give you the time worked, in seconds. Divide by 60 to get minutes, etc. Subtract the number of seconds in a normal workday to get the overtime or undertime.

Also, be aware that this code is sensitive to SET HOURS. You must make sure your time format (12- or 24-hours) matches that setting.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

Ideally you'd store datetime values for signin and signout datetimes and then can simply subtract them.

Bye, Olaf.

 

[dbMark]

Yes, you would need to include the dates to account for shifts across two or more days.

 

[newtofoxpro]

SignIN="07:30 AM"
SignOU="03:00 PM"
SignIN=IIF(RIGHT(SignIN,2)="PM" AND BETWEEN(VAL(SUBSTR(SignIN,1,2)),1,11),STR(VAL(LEFT(SignIN,2))+12,2)+SUBSTR(SignIN,3),SignIN)
SignOU=IIF(RIGHT(SignOU,2)="PM" AND BETWEEN(VAL(SUBSTR(SignOU,1,2)),1,11),STR(VAL(LEFT(SignOU,2))+12,2)+SUBSTR(SignOU,3),SignOU)
MinIn=VAL(SUBSTR(SignIN,4,2))
MinOu=VAL(SUBSTR(SignOU,4,2))
HorIn=VAL(SUBSTR(SignIN,1,2))
HorOu=VAL(SUBSTR(SignOU,1,2))
Hndl =0
IF MinOu<MinIn
MinOu=MinOu+60
Hndl=1
ENDIF
OutMin=MinOu-MinIn
OutHor=HorOu-(Hndl+HorIn)
? "Hours "+STR(OutHor,2)
? "Minutes "+STR(OutMin,2)


I try for few, use at your own risk.

 

[Olaf Doschke]

Borislav Borissov already has a good to go solution to convert to datetime: CTOT() means chartodatetime (as C is for char fields and T is for datetime fields in CREATE TABLE, this is easy to memorize).

So there is no reason to fiddle with the single parts of the time strings you have.

The point is just, this is even easier, if you would store datetime in the first place, and that would also cover the problem of midnight, if that would concern you.

Bye, Olaf.