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

#### tonywatsonhelp

##### Well-known Member
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:
Code:
``=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,

thanks

Tony

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Try:

=MOD(F2-E2,1)

Try

=MOD(F2-E2,1)

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:
Code:
``=MOD(G3-F3+(F3>G3)*24,1)``
thanks for you help guys.

Tony

In what way doesn't this work for you?

=MOD(G3-F3,1)

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

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

Replies
20
Views
586
Replies
15
Views
443
Replies
1
Views
289
Replies
11
Views
343
Replies
1
Views
435

1,221,186
Messages
6,158,414
Members
451,492
Latest member
ichinisan123

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back