Previous Topic Next Topic

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

Answer 1 / 2
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
Answer 2 / 2
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