Date / Time calculation (2000)

Topic

New Reply

OK – I give up! I want to work out the duration of an incident (i.e the number of hours and minutes from start to end – can be more than 24 hours) – I have a table with four columns (amongst others) – 2 show the start and end date and the other 2 show the start and end time. I have used =CONCATENATE(a1,a2) (for example) to combine the start date and start time together and the same for end date and end time. (This gives me the long decimal number). I figured I can them simply subtract the start date/time from the end date/time to get an answer.
For the most part this works, but (of course!) there are several ridiculous answers (4000 hours!!!) I have played with the formatting (tried the [h] thing) but nothing will get rid of these odd answers.
For info, I have copied and pasted the value (using the paste special) on some of these answers and found that the wrong answers are holding a date, whereas the correct answers simply hold a time. Also, if I show the duration in the text format (to show the decimal value), the wrongs answers are all over 1, and the correct answers under 1. If i knew what this meant I would be able to work it out I am sure….can anyone out there sus it out??
Thanks for any help as always.

Reply | Quote

Replies

You are making it too hard on yourself. Look up the DATEDIF function in Excel Help, and use “the hh mm thing”.

Reply | Quote

Sorry – doesn’t help!! Apart from that the “Help” function rarly provides any understandable “Help” and simply sends me in circles looking for what words mean (I may be synical here!), what about the times? I need an answer in hours and minutes, not days. (I mean on the spreadsheet here by the way…although the Help function does take days too!!!!)
Maybe I have misunderstood though, do I simply type in =DATEDIF(a1,a2) and it will tell me the difference between cells a1 and a2? So would a TIMEDIF work therefore??
I have to leave work now, but will return in the morning….thanks again for any and all help!

Reply | Quote

It would probably be best if you could post a small sample spreadsheet demonstrating the problem so that someone can work it over for you to see what is happening for you.

Peter

Reply | Quote

Once you are down to less than a Day, the Hours and Minutes should be straightforward. It appeared that you were perhaps getting over-compex using CONCATENATE functions. The DATEDIF formula can be applied to the Days columns and then the simple difference in the Hours and Minutes columns can be added or subtracted as appropriate.

What form are you storing your hours and minutes in? You may find it simpler to check for erroneous data if you combine your Days and Hours and Minutes into a single cell in MM/DD/YY hh:mm format. (This is one of the default Excel date formats.) It could be that you are arriving at 4000 hours becase some of your Hours and Minutes columns are being picked up as being (say) in the year 1900.

HTH

Reply | Quote

The way Excel keeps dates/times is in number of days since Jan 0, 1900 (Dec 31, 1899) as the integer part and fraction of the day for the time. Today (July 21, 2003) is the 37,823 day since Jan0, 1900. Noon today would be half of a day. Therefore, noon today is represented as 37,823.5 for an Excel date/time.

In your application, you really should have the start date and time in one cell. However, if you truly have them entered and Excel dates and Excel times (and not as text values), you should be able to just add them together to get what you need.

If we assume that the start date is in A1, the start time is in B1, the end date is in C1, and the end time is in D1, then the following formula should calculate the event time:

=(C1+D1)-(A1+B1)

If you just format that as a time, you will get the time modulo 24 hours. To get Excel to display elapsed times, you need to use a special format. Select Cells from the Format menu and then click on Custom in the list. In the “Type” box enter:

[h]:mm

with the brackets around the h. This will display the elapsed time in hours and minutes.

Reply | Quote

Well I never! I knew it would be something simple!! This method works Legare and is very easy – didn’t realise I could add a date and time together like that. (Also, my =CONCATENATE method seems to be working this morning too….don’t know what that’s about!? Maybe it saw this other way and figured out what it was meant to do!!!??)
This way is much easier though, so thank you! It will come in useful!

Reply | Quote

Just curious… what is the significance of the brackets [ ] around the h?

Reply | Quote

It’s just a convention, I don’t think that there is a deeper significance. [h] or [hh] in a custom number format stands for hours, but it is different from h or hh in that a number of hours above 24 can be displayed. h is used for a clock time, [h] for cumulating time.

In the following, remember that Excel stores a date as a number of days and a time as a fraction of a day. So 12 hours is stored as 12/24 day = 0.5.

Unformatted	h:mm	[h]:mm
0.5	12:00	12:00
1.0	0:00	24:00
1.5	12:00	36:00

Reply | Quote