difference between two time ( 24 hours format)

[khikha1]

dear all

i am calculating difference between two times ( 24 hours format ) , as per attach i am facing a problem when the time is moving from night to morning hours ( check orange color ) , i also attached the format i am using.

hope you can assist with this . thanks in advance .

 

[Joe4]

You cannot attach files to your posts. However, you can post screen images.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[khikha1]

dear all

i am calculating difference between two times ( 24 hours format ) , as per attach i am facing a problem when the time is moving from night to morning hours ( check orange color ) , i also attached the format i am using.

hope you can assist with this . thanks in advance .

my apologies attached is the data.

 

[khikha1]

You cannot attach files to your posts. However, you can post screen images.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

noted sir . i replied and attach a snapshot .

 

[Joe4]

That is because Excel doesn't like negative times, and you are trying to subtract a larger number from a smaller ones.
Excel actually stores dates/times as numbers, specifically the number of days since 1/0/1900.
So days are just numbers/integers, and time is just the fractional part of one day.

If you have Start Date in cell K2 and End Date in cell J2, this formula would handle all cases:

=IF(K2<J2,(K2+1)-J2,K2-J2)

 

[Cubist]

Modular arithmetics should work.

=MOD(K2-J2,1)

 

[khikha1]

Modular arithmetics should work.

=MOD(K2-J2,1)

thank you