SOLVED

Duda con Formula de Excel

Copper Contributor

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

22 Replies
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
I see the formula is referencing C5 in a left function which is the cell for domingo in the calendar, does the formula live in cell C3?

which cell does this formula live in?
Yes. ERROR(SI(TEXT(DATE(Calendar_Year; FILA($A 1);1);" ddd")-LEFT(C$5;2); DATE(Calendar_Year; FILA($A 1);1);"");"")
best response confirmed by AdminLirios (Copper Contributor)
Solution

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?

@AdminLirios 

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.

Thank you, Yes Sir
Yes, thank you very much.....
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!!

Looking at your workbook, in the calendar it only has days of the week i.e. Lunes, martes, miércoles, jueves y viernes, but no actual date, so how do you detect if a tree is tall or short without other trees around? (just throwing some humor into the convo)

@AdminLirios 

Sorry, I don't know how it shall be in Spanish version. In English version it is used 3 letters for weekday and formula compares 3 characters.

image.png

 

Hi @AdminLirios 

Here's how far I got to for the answer to that question:

Yea_So_0-1621292024476.png

The other Lookup Tables:

Yea_So_0-1621292806249.png

So you're just going to map out the first weeks of the month, and the formula will be:

=IF(C$4=$J6,1,IF(AND(ISNUMBER(B6),B6>=1),B6+1,""))

Yea_So_0-1621297316158.png

 

Hi @AdminLirios 

Here is the workbook with the Tools, Mapping and a Test Calendar to apply the mapping

 

cheers

@AdminLirios 

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

image.png

and summary

image.png

 

Great, Thank you very much

@AdminLirios , you are welcome

@Sergei Baklan 

 

I was merely trying to answer the question:

so what would be the value I should modify to detect the first week, because I write in the cells
indicated there and it does not return any results to me!!

and that was the answer to the question asserted above, and not to advise whether or not to use the template as is.  I was assuming the user had other operations or procedures that cannot be accomplished by using a constant date. I hope that my response answered the question above. 

@Yea_So 

Formula didn't work since template assumed Spanish which was not exactly the same as local on user machine. For example, in Spanish-Spain =TEXT(date,"ddd") returns weekday as "DO.", Spanish-Chile "DOM.", etc. and template assumes it shall be "DO"

If correct this like =LEFT(TEXT(date,"ddd"),2) template works fine, that was the only issue with it. With this small correction template automatically detects first week.

 

Perhaps better to download template localized for exactly this language or we may play with locale on PC, with that initial template shall work from the box.

Sorry, I know no one word in Spanish and have no idea which exactly Spanish is required.

Hi @Sergei Baklan 

 

All Spanish based days are the same:

Domingo Lunes Martes Miércoles Jueves Viernes Sábado
and even if the whole day word is used the left(text,2) statement will always return a DO irregardless of what the language locale is if it is a Spanish speaking locale that is.
I know this because in my dialect we use the same day names as in Spanish speaking locales, so the formula in the template is good as gold as is.
So user asked the question: 
so what would be the value I should modify to detect the first week
With the assumption that the existing formula to day two of the first week is golden, the answer to that question is to delete the first day date of the first week of each month.
For example in January in the calendar template 1/1/21 falls on a Friday the 1/1/22 falls on a Saturday, then figure out which day name it falls on in the following year and populate the first days of each of the first week in each month in order for the existing formula to work its magic.
 
Since the value in the cell of the first day of the first week for each month is a constant and not a formula, I had to illustrate it using excel, it would be long and tedious to explain. Try explaining to someone how to peel a banana using programming language.
 
When you point out that it has to do with locales, what does locales have to do with constant values? If the date is a constant value of 1/1/21, what locale should anyone use?
 

@Yea_So 

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

image.png

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.

1 best response

Accepted Solutions
best response confirmed by AdminLirios (Copper Contributor)
Solution

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?

View solution in original post