Forum Discussion
Duda con Formula de Excel
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?
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
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.
Hi AdminLirios
Here's how far I got to for the answer to that question:
The other Lookup Tables:
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,""))
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.
