Expression.Error in nested "if" statements

[_n_MarianLein]

Hi all,

I am at a bit of a loss here and need your help.

I've been trying to write a fairly complex function in M and keep getting stuck at a point here.

Main target is to use a bunch of parameters and lists to extract a service time from StartDate to EndDate.

fxWorkingDays is a custom function that's working similar to "NetWorkdays" in Excel and delivers the raw number of days from StartDate to EndDate.
However, in order to get the service time, I need the value in [hh]:mm format, i.e. 48:30. Hence I need to fiddle around with the number a bit.

The "duration" type is what I am looking for, as it's providing the correct format, but I need to add and substract some things to the calculated time.

The error I keep getting is Expression.error: The name "ServiceTime" wasn't recognized. Make sure it's spelled correctly.

Let me paraphrase my code here - I will remove the unnecessary bits, as some of it is already working fine:

(StartDate as datetime, EndDate as datetime) as duration =>
let
	<variable declaration>	
	ServiceTime = if (
		<statement that returns "false" if the ServiceTime needs to be calculated>	) then (
		ServiceTime = #duration(0,0,0,0)
	) else (
		let
			// Calculate raw number of days and multiply it with the duration of "1 day"			C_ServiceTimeInDays = #duration(1,0,0,0) * fxWorkingDays(StartDate, EndDate, {holidays}),

			// Check if StartDate needs to be substracted. If yes, mark it as such			C_StartDateIsWorkDay = if
				<statement that returns "true">			then
				#duration(1,0,0,0)
			else
				#duration(0,0,0,0),

			// Check if EndDate needs to be substracted. If yes, mark it as such			C_EndDateIsWorkDay = if
				<statement that returns "true">			then
				#duration(1,0,0,0)
			else
				#duration(0,0,0,0),

			// At this point I know what I need to do (logically)
			// I need to return the "ServiceTime", which is the result of the outer "else" case:
                        // Therefore, I need to calculate the duration, which is the "ServiceTimeInDays". Logically, this is:
                        // "C_ServiceTimeInDays" minus "C_StartDateIsWorkDay" minus "C_EndDateIsWorkDay"

			// However, this does not work and I get above error:			ServiceTimeInDays = C_ServiceTimeInDays - C_StartDateIsWorkDay - C_EndDateIsWorkDay
		in
			ServiceTimeInDays
	)
in
	ServiceTime

I believe my issue here lies in the "in" and returned statements, but I can't figure it out for the life of me...

I had also tried it with this, which brings another error message:

(StartDate as datetime, EndDate as datetime) as duration =>
let
	<variable declaration>
	
	ServiceTime = if (
		<statement that returns "false" if the ServiceTime needs to be calculated>
	) then (
		ServiceTime = #duration(0,0,0,0)
	) else (
               <without the second "let ... in ...", but still has the same calculations>
		#duration(C_ServiceTimeInDays - C_StartDateIsWorkDay - C_EndDateIsWorkDay)
	)
in
	ServiceTime

// RESULT: "Expected Token: Identifier" at this point:
               #duration(C_ServiceTimeInDays - C_StartDateIsWorkDay - C_EndDateIsWorkDay) 
              ^

In this case, if I instead type

ServiceTime = #duration(C_ServiceTimeInDays - C_StartDateIsWorkDay - C_EndDateIsWorkDay)
//RESULT: "The variable with the name "ServiceTime" is already defined in this area"

I really hope, someone can help...

[Nolock · Accepted solution]

Hi @_n_MarianLein,

remove the part with "ServiceTime =" before empty duration. 

(StartDate as datetime, EndDate as datetime) as duration =>
let
	<variable declaration>	
	ServiceTime = if (
		<statement that returns "false" if the ServiceTime needs to be calculated>	) then (
		ServiceTime = #duration(0,0,0,0)

Next point is

#duration(C_ServiceTimeInDays - C_StartDateIsWorkDay - C_EndDateIsWorkDay) 

which is definitely not correct, just use C_ServiceTimeInDays - C_StartDateIsWorkDay - C_EndDateIsWorkDay.

Btw. a similar task has been solved in Date / Time difference excluding weekends and factoring working ours, you can also inspire also there.

[_n_MarianLein]

Thanks for pointing me to that thread!
I was able to adapt this to my needs, also leveraging some of what I did in my formula.