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...

Registro de asistencia del empleado1.xlsx39 KB

admin

excel

Formulas and Functions

Yea_So
May 15, 2021
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?

SergeiBaklan

Diamond Contributor

May 19, 2021

Yea_So

Spanish - yes, maybe. I only know that for different Spanish locales Excel returns different texts, e.g.

As for template itself - yes, that could be done by different ways. However, this template is known for several years and used by many people in different countries, without need it's better not to change the logic of formulas used in this template.

Yea_So

Bronze Contributor

May 19, 2021

Hi SergeiBaklan

As for template itself - yes, that could be done by different ways. However, this template is known for several years
 and used by many people in different countries, without need it's better not to change the logic of formulas used
 in this template.

I do not subscribe to the paradigm you mentioned above, I only try to answer the question asked by the user if I know of a way to do it to guide them in whatever solution a user might be seeking. I am not a warden to the Microsoft template.

Yea_So
Bronze Contributor
May 24, 2021
Hi AdminLirios
The mapping was just an illustration on how you can create a map table of the whole first week. So you can create a table map for the first week to figure out where the first day of each month will fall on which week day name it will be to figure out where each first day of the week will be marked as Enero 1, Febrero 1, Marzo 1, Abril 1....etc.
AdminLirios
Copper Contributor
May 24, 2021
Excelentes respuestas, muy agradecido para ambos. son top top de excel, me dieron las respuestas buscadas. Feliz día.

Forum Discussion

Duda con Formula de Excel

Resources