Is there a way to reset all the date in a cell to

[tonywatsonhelp]

Hi everyone,
I have a big problem, I want to be able to calculate the number of hours between two dates in two cells, now this works fine as long as the time in each cell is not midnight or beyond, if it goes over midnight for some reason the calculation adds 21 days as you can see below:
I assume this has something to do with the formula I use which is simply:

=F2-E2

but I don't know what to do about it, I can figure out its because the dates show the end time as before the starttime in effect but I can't think of a way to get around it as not all the time are after midnight!
below is an example of what I mean

	E	F	G	H
1	start	end	how sum is caculated	hours caculated
2			00/01/1900 00:00	0:00
3	00/01/1900 12:00	00/01/1900 20:00	00/01/1900 08:00	8:00
4	00/01/1900 08:00	00/01/1900 16:00	00/01/1900 08:00	8:00
5	00/01/1900 16:00	00/01/1900 00:00	23/01/1900 08:00	560:00
6	00/01/1900 08:00	00/01/1900 14:00	00/01/1900 06:00	6:00
7	00/01/1900 00:00		00/01/1900 00:00	0:00
8	00/01/1900 06:00	00/01/1900 14:00	00/01/1900 08:00	8:00
9	00/01/1900 06:00	00/01/1900 13:30	00/01/1900 07:30	7:30
10	00/01/1900 00:00		00/01/1900 00:00	0:00
11	00/01/1900 18:00	00/01/1900 00:30	23/01/1900 06:30	558:30
12	00/01/1900 08:00	00/01/1900 16:00	00/01/1900 08:00	8:00
13	00/01/1900 12:00	00/01/1900 20:00	00/01/1900 08:00	8:00
14	00/01/1900 00:00		00/01/1900 00:00	0:00
15	00/01/1900 00:00		00/01/1900 00:00	0:00

<COLGROUP><COL style="WIDTH: 54pt" width=72><COL style="WIDTH: 96pt; mso-width-source: userset; mso-width-alt: 4096" width=128><COL style="WIDTH: 101pt; mso-width-source: userset; mso-width-alt: 4288" width=134><COL style="WIDTH: 113pt; mso-width-source: userset; mso-width-alt: 4800" width=150><COL style="WIDTH: 116pt; mso-width-source: userset; mso-width-alt: 4928" width=154><TBODY>
</TBODY>

THIS IS JUST A SNIPPET, the sheet is much large in columns and rows.

as you can see, column g shows a date and 8:00 hours the 8:00 is correct but the date is messing up when I'm trying to add these calculations together.
Now as you can imagine I'm looking to get the result of between 10:30pm and 00:30am being 2 hours, but I'm getting the calculation between the dates as well.
Now is there a way to say ignore the date just calculate the number of hours between these two days?
or if not is there a way to reset the dates in a cell back so they all show as 00/01/1900? (but not all the dates will be the same)
or can I manipulate the end date in some way so the calculation works?
I don't mind how its done but please someone help me I've been on this for over 8 hours now totally stuck!
I'm really stuck,
please help

thanks

Tony

 

[Andrew Poulsom]

Try:

=MOD(F2-E2,1)

 

[VoG]

Try

=MOD(F2-E2,1)

 

[tonywatsonhelp]

Brilliant! MOD did the job thank you so much,
had to play around with the formula a bit but got the result I needed and couldn't do it without you so thank you,
P.S. in case anyone else has the same problem the formula I used was this:

=MOD(G3-F3+(F3>G3)*24,1)

thanks for you help guys.

Tony

 

[Andrew Poulsom]

In what way doesn't this work for you?

=MOD(G3-F3,1)

 

[tonywatsonhelp]

Hi, it just wasn't giving me the number of hours, it was giving me something else, maybe I'm wrong and I was just looking at it wrong but it didn't seam to give me the hours worked.
but I couldn't have sorted it without your help, so thank you very much, I only thought I'd post the solution I used in case anyone else had the same problem, I wasn't trying to criticise your formula so sorry if it read that way. I was so stuck and you gave me the answer in just a few minutes. very grateful
Thanks

Tony

 

[Andrew Poulsom]

No criticism inferred, but can you post an example where it doesn't work and your revision does?