Forum Discussion
Duda con Formula de Excel
Estimada comunidad, gusto en saludarlos y les deseo éxitos en sus funciones / emprendimientos.
Traigo a colación la utilización de la siguiente formula:
=SI.ERROR(SI(TEXTO(FECHA(Calendar_Year;FILA($A1);1);"ddd")=IZQUIERDA(C$5;2);FECHA(Calendar_Year;FILA($A1);1);"");"")
Esta formula esta en una plantilla de excel sobre el registro de asistencia de empleados, su instrucción no esta muy clara, por ello acudo a ustedes, estaré super agradecido si me explican como la debo aplicar, que valor es que debo cambiar.
Atento
Hi AdminLirios
The formula below will add 2 if the day was Saturday, 1 if the day was Sunday otherwise just return today's date:
=IF(TEXT(DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())),"ddd")="Sat",DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))+2,IF(TEXT(DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())),"ddd")="Sun",DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))+1,DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))))
Your Formula:
ERROR(SI(
TEXT(
DATE(Calendar_Year; FILA($A 1);1);
" ddd")-LEFT(C$5;2); DATE(Calendar_Year; FILA($A 1);1);"");"")Your formula translated:
=IFERROR(TEXT(DATE(Calendar_Year, MONTH(NOW()),,1)," ddd")-LEFT(C$5,2), DATE(Calendar_Year, MONTH(NOW()),1),"")
TEXT(DATE(Calendar_Year,Month(NOW()),1)," ddd") RETURNS A = Sat LEFT(C5,2) RETURNS A = DO
your formula is stating Sat minus Do at this point in your formula
TEXT(DATE(Calendar_Year,Month(NOW()),1)," ddd")-LEFT(C$5,2) RETURNS A = #VALUE!as soon as your formula subtracts Sat from Do it starts to error out
I'm not really sure what FILA($A 1)statement in your formula is, but since it is inside the DATE Function: DATE(YEAR(),MONTH(),1)
I'm going to assume its trying to get some kind of a month for a value
I am going to assume that your date formula is trying to do a date math to skip domingo because the work week never includes a Sunday, is this a correct assumption?
22 Replies
Hi AdminLirios
Here is the workbook with the Tools, Mapping and a Test Calendar to apply the mapping
cheers
That is well known Microsoft template, I'd don't modify the formulas in it without strong need. The only it's language specific. If formula doesn't detect first date of the month that most probably means in your locale and in template short names of week days are different.
- Gracias! correcto, entonces cual seria el valor que debería modificar para que detecte la primera semana, porque escribo en las celdas que allí se indican y no me devuelve algún resultado!!
I'd return back initial formulas as in original templates
for the first 7 days =IFERROR(IF(LEFT(TEXT(DATE(Calendar_Year,ROW($A1),1),"[$-es-ES]ddd"),2)=LEFT(C$5,2),DATE(Calendar_Year,ROW($A1),1),IF(B6>=1,B6+1,"")),"") in between =IFERROR(IF(I6>=1,I6+1,""),"") for last 7 days =IFERROR(IF(AND(AK6>=1,AK6+1<=DATE(Calendar_Year,ROW($A1)+1,0)),AK6+1,""),"")When it shows correctly off-days
and summary
- Which template are you using and what is the expected result of that formula, please share more information
- En el adjunto del mensaje esta el archivo que estoy utilizando. Gracias
Hi AdminLirios
The formula below will add 2 if the day was Saturday, 1 if the day was Sunday otherwise just return today's date:
=IF(TEXT(DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())),"ddd")="Sat",DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))+2,IF(TEXT(DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())),"ddd")="Sun",DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))+1,DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))))
Your Formula:
ERROR(SI(
TEXT(
DATE(Calendar_Year; FILA($A 1);1);
" ddd")-LEFT(C$5;2); DATE(Calendar_Year; FILA($A 1);1);"");"")Your formula translated:
=IFERROR(TEXT(DATE(Calendar_Year, MONTH(NOW()),,1)," ddd")-LEFT(C$5,2), DATE(Calendar_Year, MONTH(NOW()),1),"")
TEXT(DATE(Calendar_Year,Month(NOW()),1)," ddd") RETURNS A = Sat LEFT(C5,2) RETURNS A = DO
your formula is stating Sat minus Do at this point in your formula
TEXT(DATE(Calendar_Year,Month(NOW()),1)," ddd")-LEFT(C$5,2) RETURNS A = #VALUE!as soon as your formula subtracts Sat from Do it starts to error out
I'm not really sure what FILA($A 1)statement in your formula is, but since it is inside the DATE Function: DATE(YEAR(),MONTH(),1)
I'm going to assume its trying to get some kind of a month for a value
I am going to assume that your date formula is trying to do a date math to skip domingo because the work week never includes a Sunday, is this a correct assumption?
