trouble with IF, AND, IF formula

[taraRM]

I am trying to build a formula to do the following:

IF F2 is not blank & G2 is not Blank, calculate the session hours (some go over midnight)

IF F2 and G2 are blank, leave the cell blank

Here is my current formula, that is returning an error

IF((AND(F2<>"",G2<>"")),IF((F2>G2,G2+1,G2)-F2)," ")

Version: 

Microsoft® Excel® for Microsoft 365 MSO (Version 2110 Build 16.0.14527.20234) 64-bit

Thanks for your help!

[#2]

@taraRM 

Try using IFS instead of nesting IFs

=IFS(AND(F2="",G2=""),"",G2>F2,G2-F2,F2>G2,G2+1-F2) seems to work.

Let's break it down:

=IFS(

AND(F2="",G2=""),"",

G2>F2,G2-F2,

F2>G2,G2+1-F2

)

the syntax of IFS is condition followed by result, and it stops once it meets a condition. (The pairs of condition and result can continue for many possible conditions.)

In this case, the formula is saying

IF both F2 and G2 are blank, the result is blank

IF G2 is greater than F2, do a simple subtraction of the latter from the former

IF F2 is greater than G2, add 1 to G2 (to take it to the next day) and subtract F2 from the result

[#3]

@taraRM 

Since you are using 365 you may wish to return the results as a dynamic array, in which case

= IF((start<>"")*(end<>""), MOD(end-start, 1), "")

= LET(
   completed, (start<>"")*(end<>""),
   elapsed, MOD(end-start, 1),
   IF(completed, elapsed, ""))

= MAP(start, end,
   LAMBDA(s,e,IF((s<>"")*(e<>""), MOD(e-s,1),"")))

= LET(
   Elapsedλ, LAMBDA(s,e,IF((s<>"")*(e<>""), MOD(e-s,1),"")),
   MAP(start, end, Elapsedλ)
   )

= LET(
   Elapsedλ, LAMBDA(t,IF(AND(t<>""), MOD(SUM(t*{-1,1}),1),"")),
   BYROW(times, Elapsedλ)
   )

are all working formulas, though the final three require Lambda helper functions so are, as yet, only available on the insider beta version of Excel.

Note: Start and End are names applied to the time columns whilst 'times' applies to the 9x2 array.