Another if then statement

[jonvanwyk]

=IF(OR(C5="V1",C5="V2",C5="V3"),B6-B5,B6)

In the above code, B5 is "7:00" while B6 is "11:00". Since "V1" was selected for C5, it should subtract 7:00 from 11:00 and return a value of 4 (hours). If V1 had not been selected, it should just display 11:00 (the value of B6).

[ChemistB]

Yes, that is how that formula should work (IF cell C5 is either V1, V2 or V3, then subtract B5 from B6, otherwise enter B6). So what is happening?

[jonvanwyk]

Yes, that is how that formula should work (IF cell C5 is either V1, V2 or V3, then subtract B5 from B6, otherwise enter B6). So what is happening?

Well, I just realized it is doing the math correctly, but it is converting my "4" into 4:00. I have a different spread sheet where I have a similar equation, and when the result is time, it shows time, and when the result is a integer, it shows the integer.

[martindwilson]

what is your question? that works fine

[noexcel=badwork]

hi Jonva
try this code:

=IF(OR(C5="V1",C5="V2",C5="V3"),(B6-B5+(B6<B5))*24,B6)

then change the format of your cell to general
regards,

[jonvanwyk]

hi Jonva
try this code:

=IF(OR(C5="V1",C5="V2",C5="V3"),(B6-B5+(B6<B5))*24,B6)

then change the format of your cell to general
regards,

That formula successfully converts the 4:00 into an integer of 4. However, when C5 does not equal V1, V2, or V3, it should show what is in B6, which is 11:00. Now it displays 11:00 as 0.4583333 when I change the format of the cell to general. If I leave it as time, then my integer of 4 changes to 0:00.

[noexcel=badwork]

here is the code:

=IF(OR(C5="V1",C5="V2",C5="V3"),(B6-B5+(B6<B5))*24,B6*24)

[jonvanwyk]

here is the code:

=IF(OR(C5="V1",C5="V2",C5="V3"),(B6-B5+(B6<B5))*24,B6*24)

Using this formula, I get a result of "11" instead of "11:00" when format is set to General. When I change the format to [=0]"";hh:mm I get a result of 00:00

[noexcel=badwork]

this is the code
I think this one will work:

=IF(OR(C5="V1",C5="V2",C5="V3"),(B6-B5+(B6<B5))*24,B6*24&":"&"00")

[martindwilson]

depends ,do you want to use the 11:00 in other calculations? you cant have 2 formats in the same cell
similar to above
=IF(OR(C5="V1",C5="V2",C5="V3"),(B6-B5+(B6<B5))*24,TEXT(B6,"hh:mm")) format general

[jonvanwyk]

depends ,do you want to use the 11:00 in other calculations? you cant have 2 formats in the same cell
similar to above
=IF(OR(C5="V1",C5="V2",C5="V3"),(B6-B5+(B6<B5))*24,TEXT(B6,"hh:mm")) format general

This worked :-) Thank you.