Problem calculating times for full day difference

SteveNL86

Board Regular
Joined
Nov 11, 2014
Messages
90
Office Version
  1. 2016
Platform
  1. Windows
Dear reader,

I'm trying to calculate the difference between 2 diferent times. My formula is working apart from in 1 spot.

8:00 AM8:00 AM0:00
4:00 PM11:00 PM7:00
4:00 PM11:00 PM7:00
8:00 AM4:00 PM8:00
10:00 AM6:00 PM8:00
8:00 AM4:00 PM8:00
11:00 PM8:00 AM9:00
8:00 AM1:00 PM5:00
1:00 PM6:00 PM5:00
--0:00
6:00 PM11:00 PM5:00
1:00 PM8:00 PM7:00
4:00 PM11:00 PM7:00
4:00 PM11:00 PM7:00
8:00 AM4:00 PM8:00
10:00 AM6:00 PM8:00
8:00 AM4:00 PM8:00
11:00 PM8:00 AM9:00
11:00 PM4:00 AM5:00

=IF(F2="-";0;IF(G2>F2;G2-F2;G2+1-F2))
Is the formula I'm using. But I want it to give 24 hours as results in the first row.

Any help is much appreciated!
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
64,110
Office Version
  1. 365
Platform
  1. Windows
You need to format the cells as [h]:mm
 
Solution

SteveNL86

Board Regular
Joined
Nov 11, 2014
Messages
90
Office Version
  1. 2016
Platform
  1. Windows
When I change to fractions I can see it does give me 1 as result, but changing it to h:mm doesn't seem to work. It won't show 24 hours.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
64,110
Office Version
  1. 365
Platform
  1. Windows
That is not the format I suggested ;) you need the square brackets.
 

SteveNL86

Board Regular
Joined
Nov 11, 2014
Messages
90
Office Version
  1. 2016
Platform
  1. Windows
Thank you Fluff,

I was actually running in a bit of a mess at first because Excel would keep saying it can't change to that format. Turns out thats due to my language settings.
Changing the format to [u*]:mm without the * did the trick.

Thanks for your help!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
64,110
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it & thanks for the feedback.
 

Forum statistics

Threads
1,144,612
Messages
5,725,315
Members
422,610
Latest member
sanantonio

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top