Datediff question

amysong

New Member
Joined
Nov 10, 2011
Messages
13
Hi there, I am trying to find the duration in total minutes between a start and stop time, both of which are in hh:mm military time format. The tricky part is, the end time could be the next day, and so end time could be less than start time. There are no columns defining date, just 2 columns with time of day in them (and of course other fields, but not pertinent to this).

Assuming start time is in column A and end time is in column B, I am doing an if statement for =if(B2>A2,minute(b2-a2),[something]). I know I need the "[something]" to be like b2+24hours-a2 but not sure how the syntax works for that. Thanks if you can help!
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Welcome to the board...

You sould be able to use the MOD function for this...

Try

=MOD(B2-A2,1)
 

amysong

New Member
Joined
Nov 10, 2011
Messages
13
Hmm, that seems to only give me the minutes difference excluding the hour, so if A2 is 20:30 and B2 is 6:00 then it gives me 30 minutes. I am looking for it to give me 570 minutes (9.5 hours). Thanks for your reply and sorry if I'm interpreting it wrong!
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Format the cell with the formula as custom - [m]

Excel Workbook
ABC
220:306:00570
Sheet2
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
Hmm, that seems to only give me the minutes difference excluding the hour, so if A2 is 20:30 and B2 is 6:00 then it gives me 30 minutes. I am looking for it to give me 570 minutes (9.5 hours). Thanks for your reply and sorry if I'm interpreting it wrong!
If you want the result as a numeric decimal value then:

=MOD(B2-A2,1)*24

Format as General or Number

Result = 9.5

Or...

=MOD(B2-A2,1)*1440

Format as General or Number

Result = 570
 

amysong

New Member
Joined
Nov 10, 2011
Messages
13
Ahhh, I see, I neglected to set the cells to the correct format. Thank you! I did not know about the [m] format. Good to know!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,321
Messages
5,600,950
Members
414,417
Latest member
Nobu

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