| Home |
| Search |
| Today's Posts |
|
#3
|
|||
|
|||
Date/Time Question
Thank you
"Ron Rosenfeld" wrote: On Wed, 9 Nov 2005 16:32:20 -0800, bladelock wrote: I have four columns: A B C D [Date] [Time] [Date] [Time] 01/01/2005 01:00 01/01/2005 01:05 01/01/2005 01:00 01/02/2005 01:05 01/01/2005 01:00 01/03/2005 01:05 How can I get in column "E" a formula that produces the different in time? 1. (A1+B1) - (C1+D1) = 0:00:05 next one 2. (A2+B2) - (C2+D2) = 0:24:05 next one 3. (A3+B3) - (C3+D3) = 1:24:05 I used this formula but got "#value" =TEXT((A1+B1)-(C1+D1),"d:h:mm") Can someone help? You must subtract the earlier time from the later time. You are doing the reverse. Also, be aware that the maximum "d" is 31. If your calculations result in a higher "d", it will be erroneous. If that may be a problem, try: =INT(C2+D2-(A2+B2)) &":" & TEXT(MOD(C2+D2-(A2+B2),1),"hh:mm") If you MUST have negative times, there are some workarounds, so let us know. --ron |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Benefits many people - Question | Excel Discussion (Misc queries) | |||
| How do I find and replace a question mark in Excel? | Excel Discussion (Misc queries) | |||
| date/time stamp | Excel Worksheet Functions | |||
| date/time | Excel Worksheet Functions | |||
| Hints And Tips For New Posters In The Excel Newsgroups | Excel Worksheet Functions | |||
Threaded Mode
