time- time

[balahawy]

Hi all,
really i have 2 fields with format "mmm-dd-yyyy hh:nn:ss"
i want to deduct the fields and the result to be "hh:nn:ss"

i tried it but give me 00:00:00

 

[pr2-eugin]

How are you performing this calculation?

 

[balahawy]

i put this =([Dte1]-[Dte])

 

[pr2-eugin]

Try

= Format([Dte1]-[Dte], "Long Time")

 

[balahawy]

i tried it
example = Aug-01-2014 1:00:00 PM -Aug-01-2014 1:50:00 PM
give me 12:50:00 AM
but it should give me 00:50:00 only

 

[pr2-eugin]

What? Of course it is only 00:50:00. The time difference is only 50 minutes. What are you trying to do here? How would it give 12:50:00 AM?

 

[balahawy]

i Swear it's give me 12:50:00 AM

 

[balahawy]

Dte format mmm-dd-yyyy hh:nn:ss AM/PM (Aug-01-2014 1:00:00 PM )
Dte1 format mmm-dd-yyyy hh:nn:ss AM/PM (Aug-01-2014 1:50:00 PM)
X = = Format([Dte1]-[Dte], "Long Time")
give me = 12:50:00 AM any suggestion

 

[pr2-eugin]

How about this then?

= Format(([Dte1] - [Dte]) - #00:00:00 AM#, "HH:NN:SS AMPM")

 

[balahawy]

Dte format mmm-dd-yyyy hh:nn:ss AM/PM (Aug-01-2014 1:00:00 PM )
Dte1 format mmm-dd-yyyy hh:nn:ss AM/PM (Aug-01-2014 1:50:00 PM)
X = = = Format(([Dte1] - [Dte]) - #00:00:00 AM#, "HH:NN:SS AMPM")
still doesn't work give the same =12:50:00 AM

 

[pr2-eugin]

It does for me !

? Format((#Aug-01-2014 1:50:00 PM#-#Aug-01-2014 1:00:00 PM#) - #00:00:00 AM#, "HH:NN:SS AMPM")
12:50:00 AM

 

[balahawy]

sir when i put your equation
= Format(([Dte1] - [Dte]) - #00:00:00 AM#, "HH:NN:SS AMPM") he change it automatically to
= Format(([Dte1] - [Dte]) - #12:00:00 AM#, "HH:NN:SS AMPM")
why that i don't know

 

[balahawy]

any idea

 

[pr2-eugin]

I seriously have no clue. It should work

Have you set Format on the control you are trying to calculate this difference.

 

[balahawy]

no

 

[pr2-eugin]

Could you upload a Stripped DB?

How to Upload a Stripped DB.

To create a Sample DB (to be uploaded for other users to examine); please follow the steps..

1. Create a backup of the file, before you proceed..
2. Delete all Forms/Queries/Reports that are not in Question (except the ones that are inter-related)
3. Delete auxiliary tables (that are hanging loose with no relationships).
4. If your table has 100,000 records, delete 99,990 records.
5. Replace the sensitive information like Telephone numbers/email with simple UPDATE queries.
6. Perform a 'Compact & Repair' it would have brought the Size down to measly KBs..
7. (If your Post count is less than 10 ZIP the file and) Upload it..

Finally, please include instructions of which Form/Query/Code we need to look at. The preferred Access version would be A2003-A2007 (.mdb files)

 

[balahawy]

=DateDiff("h",[Dte],[Dte1]) & ":" & (((DateDiff("n",[Dte],[Dte1])/60)-DateDiff("h",[Dte],[Dte1]))*60)


finally

 

[balahawy]

thanks for yor effort now working after my equation
=DateDiff("h",[Dte],[Dte1]) & ":" & (((DateDiff("n",[Dte],[Dte1])/60)-DateDiff("h",[Dte],[Dte1]))*60)

 

[pr2-eugin]

That just gave you 0:50? That was already given to you in Post #4

I think we have come to a miscommunication. The simple

= Format([Dte1]-[Dte], "Long Time")

Would give you this. Anyway. Glad you found another solution.

 

[balahawy]

no sir once i put = Format([Dte1]-[Dte], "Long Time") give me 12:50 why i don't know once i put
=DateDiff("h",[Dte],[Dte1]) & ":" & (((DateDiff("n",[Dte],[Dte1])/60)-DateDiff("h",[Dte],[Dte1]))*60)
give me 0:50

thx for your effort