Datediff question

[amysong]

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!

 

[Jonmo1]

Welcome to the board...

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

Try

=MOD(B2-A2,1)

 

[amysong]

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]

Format the cell with the formula as custom - [m]

Excel Workbook
	A	B	C
2	20:30	6:00	570
Sheet2

 

[T. Valko]

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]

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!