Adding times together

[Robbie3003]

I have a website that has 9 time boxes, each box is for the user to report that actual time they did something. Some of those times are then used to calculate the total time a certain operation took.

In access I have the times stored in date/time format using shorttime and I am adding these values together

The code I cobbled up is below

timetotal1: IIf([time9]=#00:00:00# And [time3a]>#00:00:00#,Format(((CLng(24*60*CDate([time3a]))-CLng(24*60*CDate([time3]))))\60,"0") & ":" & Format(((CLng(24*60*CDate([time3a]))-CLng(24*60*CDate([time3])))) Mod 60,"00"),IIf([time9]=#00:00:00#,Format(((CLng(24*60*CDate([time4]))-CLng(24*60*CDate([time3])))+(CLng(24*60*CDate([time6]))-CLng(24*60*CDate([time5]))))\60,"0") & ":" & Format(((CLng(24*60*CDate([time4]))-CLng(24*60*CDate([time3])))+(CLng(24*60*CDate([time6]))-CLng(24*60*CDate([time5])))) Mod 60,"00"),Format(((CLng(24*60*CDate([time4]))-CLng(24*60*CDate([time3])))+(CLng(24*60*CDate([time6]))-CLng(24*60*CDate([time5])))+(CLng(24*60*CDate([time9]))-CLng(24*60*CDate([time8]))))\60,"0") & ":" & Format(((CLng(24*60*CDate([time4]))-CLng(24*60*CDate([time3])))+(CLng(24*60*CDate([time6]))-CLng(24*60*CDate([time5])))+(CLng(24*60*CDate([time9]))-CLng(24*60*CDate([time8])))) Mod 60,"00")))

In simple terms if box one was 09.00 and box two was 10.00 the difference between the two is 01.00 this time is then added to the difference between the other boxes and the total output is the total time I am looking for

Even though I suspect the code above it way over the top it works exactly as I wanted, except..........

if box one the job started at 23.30 and box two the job finished 01.30 it all goes wrong and I get minus outputs which the next web page can't process and it all crashes.

All I need to do is cater for this and I am done

Can anyone point me in the right direction please

Many thanks in advance

Rob

PS I hope I made sense

 

[spikepl]

In Access, 01.30 is earlier in the day than 23.30. Access does not read minds. If a time belongs to a subsequent day you have to tell it, by adding 1 to the time:

sometimetomorrow=sometimetoday+1 'where sometimetoday is #01:30#

 

[Robbie3003]

I reaslised why this was happening as soon as it happened, but I can't work out how from just the times I can let access know its the next day, I need access to work this out due to the second box being lower than the first hence it must be a new day and then it somehow needs to know the time difference and add them together. its got me stumped

 

[Brianwarnock]

Basically if the end time is less than or equal to the start time you add 24 hours to it before doing the subtraction, of course this only works if the difference cannot be more than one day, which is why it is better to work with full date time data.

Dates are stored as double decimal numbers with the number before the decimal point giving the date relative to 31/12/1899 I think from memory and the numbers after the decimal point giving the time, which is why spikepl told you to add 1

0.75 represents 18:00
0.25 represents 06:00. Add1 to get 1.25
1.25 - 0.75=0.5 which is 12:00

Brian

 

[Robbie3003]

Most jobs are just a few hours, non would be more than 8 so it would never go over an other day. Will check this out and see where it leads me, many thanks

 

[namliam]

If your added times dont cross 24 hours you dont need the convoluted format thing, just do Format(Yourcalc, "HH:MM")

Also if some of your times are 00:00:00 the substraction will be zero anyways, so why exclude them from the calculation? Why not simply do one calculation for all?