Edite para esclarecer: a data retornada DEVE ser inferior a 24 meses da data atual porque esta é uma data de validade e os itens devem expirar no momento correto, a menos que sejam renovados. Outra forma de dizer é que a data precisa estar o mais próximo possível de um ciclo de renovação de 2 anos, sem ultrapassar 2 anos.
Postagem original: Bom dia! Tenho vasculhado o Google e não cheguei a lugar nenhum, então espero poder ajudar na criação de uma fórmula. Preciso criar uma única fórmula que não faça referência a outras células porque espero limitar o número de erros cometidos por outros usuários :)
O que eu preciso: encontre a próxima instância de 31/03 OU 30/09 que seja maior que 18 meses, mas menor que 24 meses a partir de HOJE.
Com base na frase acima, sinto que alguma combinação de HOJE, OU, <, > e um identificador de mês/dia me daria o que procuro, mas estou tendo dificuldades com a ordem das operações. Atualmente meus colegas de trabalho estão usando uma tabela (abaixo) para calcular manualmente essas datas e isso é entediante.
- Abril - setembro ímpar = março próximo ímpar
- Abril - Setembro Even = Março Próximo Even
- Out par - março ímpar = setembro próximo par
- Outubro ímpar - março par = setembro próximo ímpar
Agradecemos antecipadamente por qualquer contribuição e editarei isso se encontrar uma fórmula aproximada depois do trabalho!
Responder1
Aqui está uma solução fácil de entender, baseada em fórmulas e sem matriz, que funciona com o Excel 2010 (e versões anteriores). Ele usa colunas auxiliares (que podem ser ocultadas).
Como o requisito de <24 meses e >18 meses nem sempre pode ser atendido, e o requisito rígido é <24 meses, afrouxei o requisito na outra extremidade para >=18 meses.
Para qualquer data, existem apenas três datas alvo possíveis: 31/03 ou 30/09 no ano 18 meses a partir da data, ou 31/03 do ano seguinte. Você só precisa selecionar o primeiro que atenda aos critérios.
A pergunta especifica os resultados com base em HOJE. Queria mostrar também como isso se comporta em outros “hojes”. A célula A2 contém =TODAY(). As outras células na coluna A são apenas outras datas para ilustração; especialmente aqueles nas "datas limite" relacionadas a 31/03 e 30/09. As fórmulas fazem referência à célula de data, mas TODAY() pode ser codificado.
As colunas I:J são apenas ilustrativas. Eles mostram as datas 18 e 24 meses da coluna A data para ajudar a entender por que os valores dos resultados foram selecionados.
As colunas auxiliares são C:E. Eles contêm as três datas previstas candidatas para a data na coluna A. A meta 1 em C2 contém:
=DATE(YEAR(EDATE(A2,18)),3,31)
Isso cria a data 31/03 no ano 18 meses a partir da data da coluna A. O alvo 2 em D2 contém:
=DATE(YEAR(EDATE(A2,18)),9,30)
Isso cria a data 30/09 no ano 18 meses a partir da data da coluna A. A meta 3 em E2 contém:
=DATE(YEAR(EDATE(A2,18))+1,3,31)
Isso cria a data 31/03 no ano seguinte a 18 meses da data da coluna A.
O resultado está na coluna G. A fórmula em G2:
=SUMPRODUCT((C2:E2<EDATE(A2,24))*(C2:E2>=EDATE(A2,18))*C2:E2)
Devido aos requisitos, apenas uma data prevista será qualificada. SUMPRODUCT lida com comparações de array com uma fórmula normal (não array).
C2:E2<EDATE(A2,24)retorna VERDADEIRO/FALSO (1/0) para cada data prevista com base no fato de a data ser inferior a 24 meses da data da coluna A.
C2:E2>=EDATE(A2,18)da mesma forma, retorna 1/0 para cada data prevista com base no fato de a data ser >= 18 meses a partir da data da coluna A.
Apenas uma data prevista atenderá a ambas as condições, portanto, o produto desses valores 1/0 será 1para essa data e 0para ambas as outras datas. Esse produto é multiplicado pelo valor em cada célula da data prevista. Como as datas são armazenadas como números, o resultado é o número que representa a data prevista qualificada. Isso só precisa ser formatado como uma data.
Responder2
A seguinte função definida pelo usuário cria primeiro um período de calendário de 18 a 24 meses a partir de hoje. Em seguida, ele percorre o período até encontrar uma data que corresponda aos seus critérios:
Public Function ProjDate() As Date
Dim d1 As Date, d2 As Date, y As Long
Dim dd As Date, d As Long, m As Long
d = Day(Date)
m = Month(Date)
y = Year(Date)
d1 = DateSerial(y, m + 18, d + 1)
d2 = DateSerial(y, m + 24, d - 1)
For dd = d1 To d2
d = Day(dd)
m = Month(dd)
If (m = 3 And d = 31) Or (m = 9 And d = 30) Then
ProjDate = dd
Exit Function
End If
Next dd
End Function
Funções definidas pelo usuário (UDFs) são muito fáceis deinstalarE use:
- ALT-F11 abre a janela VBE
- ALT-I ALT-M abre um novo módulo
- cole o material e feche a janela do VBE
Se você salvar a pasta de trabalho, a UDF será salva com ela. Se você estiver usando uma versão do Excel posterior a 2003, deverá salvar o arquivo como .xlsm em vez de .xlsx
Pararemovera UDF:
- abra a janela VBE como acima
- limpar o código
- feche a janela do VBE
Parausara UDF do Excel:
=minhafunção(A1)
Para saber mais sobre macros em geral, consulte:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
e
http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx
e para obter detalhes sobre UDFs, consulte:
http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx
As macros devem estar habilitadas para que isso funcione!
Responder3
Aqui está uma fórmula. Conforme escrito, para teste, ele faz referência ao A1. No entanto, você pode substituir A1por TODAY()se achar que ele retorna os resultados esperados:
=MAX((MONTH(EDATE(A1-DAY(A1)+1,{18;19;20;21;22;23;24}))={4,10})*EDATE(A1-DAY(A1)+1,{18;19;20;21;22;23;24}))-1
O acima é umvariedadeFórmula.
Como esta é uma fórmula de matriz, você precisa “confirmá-la” mantendo pressionado ctrl+ shiftenquanto pressiona enter. Se você fizer isso corretamente, o Excel colocará colchetes {...}ao redor da fórmula, conforme observado na barra de fórmulas
Se quiser evitar o CSEprocedimento de entrada, você pode tentar um pouco mais longo:
=AGGREGATE( 14,4,(MONTH(EDATE(A1-DAY(A1)+1,{18;19;20;21;22;23;24}))={4,10})*EDATE(A1-DAY(A1)+1,{18;19;20;21;22;23;24}),1)-1