Redefinir o número da sequência com a mudança do mês

Question

(Célula A1) Nº de referência da fatura EPS-I-S151001

(célula B1) Data da fatura 05 / out / 15

Quando a data da fatura for inserida manualmente, o número de referência da fatura será gerado automaticamente.

O formato da Referência No é o seguinte: EPS-I-S151001 .

"EPS-I-S" são caracteres fixos por toda parte.

Primeiros 2 dígitos, "15"="Ano"

próximos 2 dígitos, "10"="mês"

Últimos 2 dígitos, "01" = Executando números de 01 em diante.

Por exemplo:

Ref de fatura Não Data da fatura

EPS-I-S151001 10-Nov-15

EPS-I-S151002 15-Nov-15

EPS-I-S151003 30-Nov-15

Os últimos 2 dígitos continuarão a ser executados em sequência.

Os primeiros 4 dígitos serão afetados por qualquer alteração no mês ou no ano. Qualquer alteração no mês ou no ano solicitará que os últimos dois números sejam redefinidos de volta para 01 .

Nº de referência da fatura Data da fatura

EPS-I-S151001 10-Nov-15

EPS-I-S151002 15-Nov-15

EPS-I-S15 1201 22- Dez -15 (Novo Mês)

EPS-I-S151202 23-Dez-15

EPS-I-S151203 25-Dez-15

EPS-I-S16 0101 03- Jan - 16 (Ano Novo / Mês)

EPS-I-S160102 15-Jan-16

EPS-I-S160103 22-Jan-16

    

Answer 1

Acho que temos uma solução para você:

Ignoreoformatodedataligeiramentediferente,éapenasformatação.EuprecisavadeixarumaprimeiralinhadedadosembrancoporqueadataécomparadacomalinhaanteriorequeprecisavaestarembrancoouumadatanoLibraOfficeCalc(pularissopodefuncionarnoExcel,nãotinhaacessoprontoparatestarisso).Umaalternativaseriacodificaroprimeironúmerodefaturanalinha2.

Então,oiníciodafórmulaemA3seria:

=IF(ISBLANK(B3),"","EPS-I-S"&TEXT(B3,"yy")&TEXT(B3,"mm")&TEXT(IF(MONTH(B3)=MONTH(B2),VALUE(RIGHT(A2,2))+1,1),"00"))

Copie essa coluna para baixo A até onde precisar. As células permanecerão em branco até que você insira uma data na coluna B. Isso é o que o teste ISBLANK faz no início. Você já descobriu os próximos termos, embora tenha removido quando atualizou a questão. Eles fornecem o prefixo, ano e mês.

Isso nos leva ao número de sequência. Uma mudança no ano também envolve uma mudança no mês (supondo que você não passe um ano inteiro até a próxima fatura, caso em que você teria coisas maiores para se preocupar do que esta planilha). Portanto, se a data estiver no mesmo mês da data anterior, ela adicionará 1 ao número. Caso contrário, ele começa com 1 .

    

Answer 2

Problemas como esse são muito mais fáceis de resolver se você usar "colunas auxiliares" - colunas que contêm resultados intermediários que você não deseja exibir mas isso vai para a construção do valor que você quer. Para esse problema, usarei uma coluna auxiliar e seu conteúdo será óbvio assim que você os vir. Usarei a coluna G . Referindo-se a uma versão anterior da sua pergunta, Eu suponho que seus dados começam na linha 3, e assumirei que a célula G2 está em branco.

• Definir G3 para =IF(TEXT(B3,"yymm")<>TEXT(B2,"yymm"), 1, G2+1)
  Isto diz “Se a data da fatura nesta linha ( B3 ) tem um ano ou mês diferente da data da linha anterior ( B2 ), em seguida, defina G3 para 1 ; caso contrário, adicione 1 ao valor anterior ( G2 ). ” Ou seja, esta é uma série em execução que é redefinida para 1 sempre que houver um novo mês.
• Definir A3 para =IF(B3="", "", "EPS-I-S" & TEXT(B3, "yymm") & TEXT(G3, "00"))
  Isso diz “Se a data da fatura nessa linha (célula B3 ) estiver em branco, exibir nada. Caso contrário, crie o número de referência da fatura usando a string constante, o ano e o mês a partir da data na coluna B , e o número único da coluna G (formatado como um número de dois dígitos, com um zero inicial, se necessário). ”

Em seguida, arraste / preencha as fórmulas nas Colunas A e G para baixo tanto quanto você quiser.

Nota: depois de ter tudo funcionando, você pode ocultar as colunas auxiliares.