asked on

How do i convert minutes from midnight into a time HH:MM:SS and then calculate i time diffrence from a start and finish time.

Hello,

In the database we have a field that stores time as minutes from midnight, in crystal i need to convert this from minutes from midnight to a time i.e. 480 minutes from midnight is 08:00, then i have a finish time of 960, i also have a start time for break of 720 and a end time for break of 738.

Their is a field that says what's the start and finish which are as follows:-
1 is start
3 is start break
4 is end break
2 is finish

Now after i've converted these minutes from midnights into times i need to find the time diffrence.

My main goal is to find how many hours are worked in the week.

Thank you!

peter57r

Hi slug0r,
I assume there is a field which holds the date?

Pete

SOLUTION

dkDeveloper

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

slug0r

ASKER

Hello Pete,

Yes there is a field that holds dates.

dkDeveloper

Hi slug0r,

If there is also a field that holds only the minutes you can use the formula I supplied above substituting the minutes field for the NumberVar mins. If not, just strip the minutes out using the Right(datefield, 3) function, and apply that value to the mins variable.

Finally, if you store the minutes for each of the fields you defined, you could just calculate the difference and apply the same hour/minute parsing to the number to determine how much time had elapsed between the two values.

Please let us know if you need more detail around any of this!

Cheers!
dK

slug0r

ASKER

Hello dkDeveloper,

Just one detail i'm not understanding, where do i put the field into this formula?.

Thanks for the help!

peter57r

In the report header you declare a variable in a formula:

Whileprintingrecords
Numbervar MinTots;
MinTots:=0;
""

In the detail line you have another formula:

Whileprintingrecords
Numbervar MinTots;
If type = 1 then
MinTots:= mintots - Mins
else if type = 2 then
MinTots:= mintots + Mins
else if type = 3 then
MinTots:= mintots + Mins
else if type = 4 then
MinTots:= mintots - Mins
else
MinTots:= mintots;
""

In the report footer you can convert to hours and minutes:

Whileprintingrecords
Numbervar MinTots;
"Total Time = " & mintots\60 & " hrs " & mintots mod 60 & " Mins"

Pete

dkDeveloper

I would construct four formulas... a formula for each start and end time for the day and the break. Place those start and end times where ever you want to view them. That will display the time of day each activity occurs.

To calculate the number of hours worked will depend on how the report is outlined/designed. Assuming each employee has his/her own details section then you could maintain a total minutes variable like Pete has suggested above, and then translate to hours using the formula I supplied above. Pete's formula will definitely get the idea across but I believe the formatting might leave some artifacts behind (like .00 and missing leading 0 on the minutes).

Hope that helps clear things up. If you need more specific answers please tell us a bit more about how your report is laid out.

Thanks!
dK

slug0r

ASKER

Hello,

Every time i try this as a formula i get that there is a error in the formula.. (lines 2 3 and 4)

Whileprintingrecords
Numbervar MinTots;
MinTots:=0;
""

peter57r

Sorry - missed out the ; at the end of the first line.

Pete

slug0r

ASKER

Hello again,

In this formula pete is there a way of making it total in groups, i want total hours for a employee and then i want to seprate the holiday hours he has had from this.

I've got it upto a point where it tells me the total hours worked for everyone, but i want total hours by employee.

Cheers,
Phil.

ASKER CERTIFIED SOLUTION

peter57r

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

slug0r

ASKER

Hello Pete,

I've done a select expert and selected one employee 2002 and the two formulas for totals give two diffrent figures, any ideas why?.

I also have a field that has H inside if the person is a holiday, can you show me how i have a total for the holidays (in employee totals) and minus the holiday total from the employee total.

Help is much appreciated im sure this is worth more than 500points :)

peter57r

Sorry, my copy and pasting was incomplete before...
Try...
If type = 1 then
(MinTots:= mintots - Mins;
EmpTots:= Emptots - Mins;)
else if type = 2 then
(MinTots:= mintots + Mins;
EmpTots:= Emptots + Mins;)
else if type = 3 then
(MinTots:= mintots + Mins;
EmpTots:= Emptots + Mins;)
else if type = 4 then
(MinTots:= mintots - Mins;
EmpTots:= Emptots - Mins;)
else

I think you post a new question for any further enhancements.

Pete

slug0r

ASKER

This didn't work for nightshift people starting 00:00 and finishing at 06:00

peter57r

What went wrong?

Pete

slug0r

ASKER

Someone working from 1080 minutes past midnight and finishing 360 minutes past midnight, the hours would be in minus figures, also the break time would start 0 minutes from midnight and minish 30 minutes past midnight.

Maybe i miss understood something.

peter57r

You should have said that times can run over midnight at the outset. The solution would probably have been different.

At this stage all I am going to do is fix the formulas so that they will work correctly. You will need to put your own date field into the formula where MyMins is calculated.

In the detail line ........:

Whileprintingrecords
Numbervar MinTots;
Numbervar EmpTots;
Numbervar MyMins;
MyMins:=Datediff("n",#01/01/2000#, {yourtable.yourdatefield}) + mins;

If type = 1 then
(MinTots:= mintots - MyMins;
EmpTots:= Emptots - MyMins;)
else if type = 2 then
(MinTots:= mintots + MyMins;
EmpTots:= Emptots + MyMins;)
else if type = 3 then
(MinTots:= mintots + MyMins;
EmpTots:= Emptots + MyMins;)
else if type = 4 then
(MinTots:= mintots - MyMins;
EmpTots:= Emptots - MyMins;)
else
(MinTots:= mintots;
emptots:=emptots;);
""
Pete