Cálculo com Datas

Question

Prezados:

Mais uma vez venho pedir de vocês, tenho que montar uma planilha e não tenho ideia por onde começar.

Pode ser com fórmulas ou macros.

Para facilitar utilizarei números no lugar de datas.

Minha planilha precisa de 100 períodos e os multiplicadores variam.

Agradeço muito quem puder me ajudar.

O que necessito é retirar concomitância entre períodos de datas de acordo com os melhores multiplicadores conforme segue:

Dt Inicial          Dt Final     Multiplicador            Resultado Esperado

10                    20                  3                        08 dias x 3 = 24 -> ou seja, do dia 10 ao dia 17

18                    40                  4                        23 dias x 4 = 92 -> ou seja, do dia 18 ao dia 40

20                    30                  1                        nenhum dia aproveitado, os demais períodos tem multiplicadores maiores

35                    70                  2                       20 dias x 2 = 40 -> ou seja, do dia 41 ao dia 50 e do dia 61 ao 70

50                    60                  8                       11 dias x 8 = 88 -> ou seja, do dia 50 ao dia 60

80                    90                  2                       11 dias x 8 = 88 -> ou seja, do dia 80 ao dia 100

85                    87                  2                       nenhum dia aproveitado,os dias 85 a 87 já foram utilizados

Grato

Adriano

Answer 1

Por que do dia 10 ao 20 existem apenas 8 dias e por que do dia 50 ao 60 vocês considerou todos os 11 dias?

---

http://www.ambienteoffice.com.br - http://www.clarian.com.br

Answer 2

Felipe, obrigado pela atenção

O segundo período tem um multiplicador maior e começa no dia 18, então, do primeiro período, de 10 a 20 só aproveito do dia 10 ao dia 17.

Do dia 50 ao 60, o multiplicador do quinto período é o maior, então, aproveito todos os dias.

Veja que os dias 50 a 60 também estão no quarto período, mas tem um multiplicador menor.

Grato

Adriano

Answer 3

Na verdade, o seu problema é um pouco complexo e eu resolveria por VBA. É necessário fazer uma rotina que verifique em todas as linhas quais são os melhores multiplicadores de cada dia.

Considere que seu cabeçalho comece na linha A1.

Crie uma classe chamada cDateElement e ponha o código a seguir:

Option Explicit

Public TopMultiplier As Long

Num módulo regular, coloque o código abaixo:

Option Explicit

Sub Main()
    Dim ws As Worksheet
    Dim iRow As Long
    Dim iDay As Date
    Dim iKey As String
    Dim iDateElement As cDateElement
    Dim iCurrentMultiplier As Long
    Dim DateElements As Object 'Scripting.Dictionary
    Dim iValidDays As Long
    
    Set ws = ActiveSheet
    Set DateElements = CreateObject("Scripting.Dictionary")
    
    With ws
        For iRow = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
            For iDay = .Cells(iRow, "A") To .Cells(iRow, "B")
                iKey = CStr(CLng(iDay))
                iCurrentMultiplier = .Cells(iRow, "C")
                
                If DateElements.Exists(iKey) Then
                    Set iDateElement = DateElements(iKey)
                    
                    If iDateElement.TopMultiplier < iCurrentMultiplier Then
                        iDateElement.TopMultiplier = iCurrentMultiplier
                    End If
                Else
                    Set iDateElement = New cDateElement
                    iDateElement.TopMultiplier = iCurrentMultiplier
                    DateElements.Add iKey, iDateElement
                End If
                
                Set iDateElement = Nothing
            Next iDay
        Next iRow
    
        For iRow = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
            For iDay = .Cells(iRow, "A") To .Cells(iRow, "B")
                iKey = CStr(CLng(iDay))
                iCurrentMultiplier = .Cells(iRow, "C")
                
                If DateElements.Exists(iKey) Then
                    Set iDateElement = DateElements(iKey)
                    
                    If iDateElement.TopMultiplier = iCurrentMultiplier Then
                        iValidDays = iValidDays + 1
                        DateElements.Remove iKey
                    End If
                End If
            
            Next iDay
            
            .Cells(iRow, "D") = iValidDays
            iValidDays = 0
        Next iRow
    
    End With
End Sub

Agora, basta escrever a fórmula =C2*D2 na célula E2, copiá-la e colar abaixo pela coluna.

---

http://www.ambienteoffice.com.br - http://www.clarian.com.br

Answer 4

Felipe, obrigado mesmo pela grande ajuda e atenção, mas está dando erro.

Só de preencher as células, nada acontece, se mando executar a macro dá erro na linha

Dim iDateElement As cDateElement

E aparece a caixa de erro :

Erro de compilação:

"O tipo definido pelo usuário não foi definido"

O que fazer?

Acho que o problema é no nome da classe, não estou conseguindo colocar o nome cDateElement, e o nome atribuido é Classe1, se tento mudar a parece a mensagem "É impossível executar a operação solicitada".

Coloquei o nome DateElement, e alterei todos os cDateElement do código para DateElement, ou seja, retirei o "c" e parece que funcionou, algum problema no que fiz?

Grato

Adriano

Answer 5

Provavelmente você não nomeou a classe com esse nome.

De qualquer forma, escrevi um artigo deste problema porque achei bom em http://ambienteoffice.com.br/blog/otimizar-datas-considerando-peso/

---

http://www.ambienteoffice.com.br - http://www.clarian.com.br

Answer 6

Felipe,

Muito obrigado pela ajuda, era isso mesmo que precisava, e parabéns pelo artigo.

Mas tem um problema,

utilize os seguintes dados

61   70     1

61   80    1,4

ou entao

61   80   0,6

61   70   1

os resultados dão errados, a impressão que tenho é que o excel não reconhece os multiplicadores menores que 1,5.

Entretanto, se utilizar

61   80   1,6

61   70   2

o resultado também dá errado, sendo os multiplicadores superiores a 1,5

Teria solução para isso? Para mim, isso seria um problema, pois meus multiplicadores não são inteiros.

Grato

Adriano

Answer 7

Acho que se você alterar

Dim iCurrentMultiplier As Long

Para:

Dim iCurrentMultiplier As Single

Será suficiente.

EDIT: mude no módulo de classe também para: 

Public TopMultiplier As Single

---

http://www.ambienteoffice.com.br - http://www.clarian.com.br

Answer 8

Isso mesmo, mais uma vez, muito obrigado e parabéns pelo artigo.

Adriano

Answer 9

Felipe, sem querer abusar, seria possível esse código trabalhar com o ano comercial de 360 dias e meses de 30?

Adriano

Answer 10

Como seria isso? Não consigo visualizar.

---

http://www.ambienteoffice.com.br - http://www.clarian.com.br

Answer 11

Não sei como funciona, mas tenho uma planilha com macro que faz isso, acho que exclui todos os dias "31" e inclui "29" e "30" de fevereiro.

Como faço para postar a planilha aqui no fórum?

Grato

Adriano

Answer 12

Para disponibilizar um arquivo aqui, entre em http://www.onedrive.com. Você será redirecionado para um gerenciador de arquivos na sua conta Microsoft. Esse é o seu diretório virtual do OneDrive.

Selecione o arquivo que quer disponibilizar e carregue no site. Depois que o arquivo for carregado, selecione-o e clique em 'Compartilhar'. Clique, em seguida no botão "Obter um link" e selecione a opção "Somente exibir". Copie esse link e cole aqui.

---

http://www.ambienteoffice.com.br - http://www.clarian.com.br

Answer 13

Felipe, segue o link

https://onedrive.live.com/redir?resid=56E619E4FCA08F9F!119&authkey=!AF1COSF4plvFmAw&ithint=file%2cxls

Para o meu uso, necessito que os anos tenham 360 dias e os meses 30.

Grato

Adriano

Answer 14

Quando baixa o arquivo a macro está sendo excluída, mas é esse o código que fica em um módul.

Nesta eu retiro as concomitancias na mão.

Function ELAPSED(StartDate As Date, EndDate As Date, ReturnType As Integer)
    Dim StartYear As Integer
    Dim StartMonth As Integer
    Dim StartDay As Integer
    Dim EndYear As Integer
    Dim EndMonth As Integer
    Dim EndDay As Integer
    StartYear = Year(StartDate)
    StartMonth = Month(StartDate)
    StartDay = Day(StartDate)
    EndYear = Year(EndDate)
    EndMonth = Month(EndDate)
    EndDay = Day(EndDate)
    
    If EndDay < StartDay Then
        EndDay = EndDay + 30
        EndMonth = EndMonth - 1
    End If
    If EndMonth < StartMonth Then
        EndMonth = EndMonth + 12
        EndYear = EndYear - 1
    End If
    Select Case ReturnType
    Case 1 '\ return year
        ELAPSED = EndYear - StartYear
    Case 2 '\ return month
        ELAPSED = EndMonth - StartMonth
    Case 3 '\ return day
        ELAPSED = EndDay - StartDay + 1
                End Select
    
    
End Function

Answer 15

Adriano,

Essa adaptação precisa de um código que teria que considerar que certos períodos de início e de fim não são numéricos, aumentando a complexidade. Não disponho de tempo para implementar, lamento.

---

http://www.ambienteoffice.com.br - http://www.clarian.com.br

Answer 16

Entendo, posso entrar em contato contigo fora do fórum?

Answer 17

Felipe, como posso entrar em contato com você? Gostaria que me indicasse alguém que pudesse fazer, de forma particular, esse código para mim.

Grato

Adriano

Answer 18

Opa, me desculpe.

Envie um e-mail para felipe arroba ambienteoffice ponto com ponto br que posso te indicar.

---

http://www.ambienteoffice.com.br - http://www.clarian.com.br

Answer 19

Email enviado.

Grato pela atenção e no aguardo da ajuda.

Adriano

Answer 20

Felipe, recebeu meu email?