Sum of Start Time and End Time, could anyone pls help me...tnx

[homer.favenir]

gud day.

i have 2 files, start time and end time.
i need to calculate how many hours from start time to end time.
and also i need the total min.

i know this is just a simple formula, but i still need your help
please help...

thanks

 

[odin1701]

Minutes:

DateDiff("n",[Start Time],[End Time])

Hours:

(DateDiff("n",[Start Time],[End Time])/60)

 

[homer.favenir]

thanks odin!!!
but how about if the start time is 6am and the end time is 5am of the next day?
the difference should be 23 hrs.

thanks

 

[gemma-the-husky]

its not simple

date/time is actually stored as a date datatype - which is a real decimal number starting at a particular date in 1890 I thnik offhand (try showing date(0))

so that a number 18560.123 will give oyou a date (18560 days from start) and a time (.123 of a 24hr day.

Now when you add times they are modular to 24hrs so

eg 10am + 4pm = 10hrs + 16hrs = 26 hrs = 2hrs (mod 24 hrs)
so you get the answer 2hrs, and you can NEVER get an answer above 24hrs

subtraction is even stranger

3am - 4pm

is 3 hrs - 16 hrs = -13 hrs = 11 hrs (mod 24)
so you get 11hrs.

its sometimes hard to see exactly how access gets its answer!

-------------
you can always treat the time as a double, and add/subtract/multiply by 60 etc - then you get a proper decimal representation of whatever you are trying to do. But if you leave them in native access date/time types you will always find it tricky to unravel

 

[homer.favenir]

thanks for the reply husky,
can i just send you my db so that you can show me how.
pls see attachments



thank you very much!

 

[ansentry]

Have a look at your sample now;

 

[homer.favenir]

thanks John A!!!
thank you very much!
your good!

how did you get that?
can you please explain me.

you added another field in query "answer", right?
and how can i add it on my report? should i add a text box and copy the formula from the form?

thanks a lot again.

homer

 

[ansentry]

you added another field in query "answer", right?
and how can i add it on my report? should i add a text box and copy the formula from the form?

The field "Answer" appears in your field list, so you can add it to the reports. Your can also go into the query and change the name of the field if you wish.

1. Have a look at the query that is the source for the form.
2. Have a look at the form in design view and you will see how the Hours, Minutes, Total Hours etc work.

 

[homer.favenir]

kewl!!!
thanks again!!!

 

[homer.favenir]

when i export the data to excel the "Answer" becomes decimal and not time.
i already change the properties of the "Answer" in the report, but it didnt work.
what should i do?

tnx

 

[ansentry]

Have a look at the sample now.

It now exports a query to excel instead of a report and the formatting is fine.

There may be a way to make it work using "export report" but I don't know how. I have never exported reports, only queries & tables.

I have made it so that each time it exports the query to excel the file is given a different name and then access opens the new excel file. If you don't want this to happen then remove the code that make it work.

 

[homer.favenir]

thank you very much john!!!
it helps me a lot and solve my problem...

thanks!!!
homer