Hello All...first time in Excel area as normally in Access but I have hit a problem with an excel sheet and times.

I know the time in Day 1 but want the total time that elapses to a time in day 2.

eg Start 06:30:00 Day 1 and Finish Day2 09:30:00 = 27:00:00

=TEXT(D1-G1,"h:mm:ss") is the formula I am using but of couse it cannot see day 2 and gives me a wrong answer as 03:00:00 instead of 27:00:00.

Any way I can add 24 Hours or do I need to use dates as well.

Regards

Alexander

Alexander,

It appears that you're storing just the time in D1 and G1, without any indication of date. Therefore, they are both being stored as times on the same day.

The second problem is that you are displaying the result as a date as well, not as a count of hours, minutes, and seconds. So, even if you DID store the date value with the start/end times, the answer would be "one day and three hours later", but you're formatting the result to cut off the bit about "one day". So it would still read as simply "three hours."

If you just want to display the number of hours/minutes/seconds, and won't be doing any math on the value, you could create a User Defined Function to return the value as a string. Let me know if you want to go this route, and I can help you out with the VBA.

Hope this helps,

Dennis

Thanks Dennis... I could add the date as a separate column if that helps??

Not sure on the VBA but willing to try.

I am out of the office until the morning but will be back to you with further comments...thanks for the help.

The columns are Name...Start Time.....Finish Time

If the Total Time was over 24 hours I wanted to be able to add 24 hours as existing formula works fine.

Regards

Alexander

if you enter your data as such...


Cell A1 = 7/27/2010 6:30:00 AM
Cell B1 = 7/28/2010 9:30:00 AM

You could then use this formula

=(DAY(B1)-DAY(A1))*24 + HOUR(B1-A1)

You will have to make sure you have the right cell format so that it display a number and not a time.

EDIT:

If you use the extra columns...

Start Date (Column D), Start Time (Column E), End Date (Column F), End Time (Column G)

Then you can use this formula

=DAY(F10-D10)*24+HOUR(G10-E10)

This post has been edited by Bob G: Jul 28 2010, 10:36 AM

If you enter data as Bob has suggested:

Cell A1 = 7/27/2010 6:30:00 AM
Cell B1 = 7/28/2010 9:30:00 AM

Then just use the formula

=B1-A1

And format this cell with the custom format [hh] for just hours (Rounded down) or [hh]:mm for hours and minutes.

Or if you just want a decimal hours, then

=ROUND((B1-A1)*24,0)

Adjust the decimal places for the minutes (These will be decimal)

HTH
Stu

Alexander,

You've basically got three choices:

1. Add TWO columns, to indicate (1) the start date, and (2) the end date, or
2. Always assume that the start date takes place on the day before the second date. No exceptions.
3. Include the date AND the time when you enter them in columns D & G.

Here's a basic User-Defined Function that you can add to your workbook, and use just like any other function:



If you go with option #1, you'd use this sort of syntax:
=TotalTime(C1+D1,F1+G1)

Where C1 is the start date, D1 is the start time, F1 is the end date, and G1 is the end time.

If we go with option #2, where we always assume the start date is one day before the end date, we'd use this sort of syntax:

=TotalTime(D1,G1+1)

By adding 1 to a date value, you increment it by 1 day, since Excel stores dates as a number of days since (I believe) January 1, 100.

Finally, if we go with option #3, where you include the dates with the times in a single cell:

=TotalTime(D1,G1)

Hope this helps,

Dennis

Hi Alexander

Just set the fomula to:

= D1-G1

and then the cell format to the Excel Custom Format

[h]:mm

I think it is inbuilt in the "Custom Format" section of my Excel installation (Excel 2003) but if [h]:mm is not available in your Custom Format, you can define it in the Cell Format dialog.

The format [h]:mm allows you to display duration of more than 24 hours.

VTDinh,

Wow, that's totally cool. I had no idea such a thing existed. Makes my code totally useless. Ah, live & learn!

Alexander,

Remember, you still need to account for the different dates. If you aren't storing the dates, but you know they're always a day apart, use VTDinh's formatting, and just change your formula to:

=(1+D1)-G1

Hope this helps,

Dennis

To Dennis, Van, Stu and Bob...you guys are great...really appreciate the time and thought and effort that goes into the solution and your clear explanations.

In fact the timing actually goes to eg 07:04:34.25 decimals of a second but I have not included them as this starts to make it more complex.

Thanks again and it is encouraging for basic users to see that the experts can learn from each other but the positive thing is your willines to pass on the knowledge and nothing beats an actual live example when a solution is required.

Thanks Again

Alexander

You're welcome, Alexander... Glad we could contribute some ideas...