Status
Not open for further replies.
1 - 6 of 6 Posts

#### texboy

·
##### Registered
Joined
·
136 Posts
Discussion Starter
Hi all,

Can someone help me with the following:
I need a formula that can return the total number of hours between dates/times, e.g.:
10-20-05 10:00am thru 10-20-05 12:00pm = 2hrs

ReeKorl are you there?

Any help is appreciated.

#### texboy

·
##### Registered
Joined
·
136 Posts
Discussion Starter
Here's a better example:

7/5/2005 7:26AM 7/5/2005 8:23 AM 0.96

7/5/2005 9:04 AM 7/5/2005 11:05 AM 2.02

#### ReeKorl

·
##### Superhuman Computer
Joined
·
1,632 Posts
I am here!

OK, the way Excel works with dates/times is completely numeric, in the sense that midnight on a day is an integer, and midday is something .5.

You can use this to your advantage by doing the following: multiply the two cells by 24, then do one minus the other.

For example, today's date is 13OCT2005 - this is equal to 38638 in excel.

Midday today is 38638.5

2pm tomorrow is 38639 + (14/24) ........(this is for 14 hours in a total of 24, as 2pm = 14:00)

so the two values, but multiplied by 24 are:

927324 and 927350

The second minus the first equals 26 - which is the amount of hours between midday today and 2pm tomorrow.

Hope this helps..... if not, post back and I'll try to make an example sheet and upload it.

#### texboy

·
##### Registered
Joined
·
136 Posts
Discussion Starter
Thanks ReeKorl,

ReeKorl said:
You can use this to your advantage by doing the following: multiply the two cells by 24, then do one minus the other.

Hope this helps..... if not, post back and I'll try to make an example sheet and upload it.

It seems to confusing though. Can you elaborate more or even better give me an example (formula) in Excel.

Can you upload sheet.

#### ReeKorl

·
##### Superhuman Computer
Joined
·
1,632 Posts
One example coming up.... well three actually.

OK, as the site only allows certain types of file to be uploaded, the file attached is called hoursdifference.xls.txt - get rid of the .txt on the end and you should be able to open it in Excel.

Hopefully the spreadsheet should be self-explanatory.

I've put formatting in there to make it look like 2 hrs and 30 minutes (for example) instead of 2.5 hrs. If you don't want it to look like that, simply change the formatting from "custom: # ?/60" to numbers.

As always, any questions just post back and I'll be happy to explain.

#### Attachments

• 20.5 KB Views: 24

#### texboy

·
##### Registered
Joined
·
136 Posts
Discussion Starter
Thank you ReeKorl,

I'll review your table.

1 - 6 of 6 Posts
Status
Not open for further replies.