Nested IF Formulas with Times not caclulating correctly

Closed
MAUTUMNS
Posts
2
Registration date
Friday March 29, 2013
Status
Member
Last seen
March 29, 2013
- Mar 29, 2013 at 02:17 PM
Kevin@Radstock
Posts
42
Registration date
Thursday January 31, 2013
Status
Member
Last seen
April 26, 2014
- Mar 29, 2013 at 02:56 PM
I have several start/end times and need to also calculate the difference in minutes of those times on occasion.


START A END A START B END B
03/01/13 14:25 03/01/13 16:54 03/01/13 16:52 03/01/13 16:59
03/01/13 11:36 03/01/13 13:11 03/01/13 11:16 03/01/13 13:15

My scenarios are: Does START B begin prior to START A? If YES, Time difference? This I am able to accomplish. I am getting stuck with: Does START B begin prior to END A? If I have a YES, I need it to give me the difference in minutes, and I am unable to get the correct number of minutes when it's over 60, which is the second row of times in my data.

2 replies

MAUTUMNS
Posts
2
Registration date
Friday March 29, 2013
Status
Member
Last seen
March 29, 2013

Mar 29, 2013 at 02:20 PM
I should add that this is what I currently have for my formula:

IF(Q15="NO","NA",IF(P15<60,MINUTE(P15),IF(P15>60,SUM(MINUTE(P15+60)))))

I hope I am missing something obvious as it seems to work correctly for minutes less than 60.
0
Kevin@Radstock
Posts
42
Registration date
Thursday January 31, 2013
Status
Member
Last seen
April 26, 2014
9
Mar 29, 2013 at 02:56 PM
Hi MAUTUMNS

Not sure if I understand correctly!

=IF(StartB<EndA,(EndA - StartB)*1440,"")

Is something that you can work with!

Kevin
0