Fórmula e funções do Excel Parte 7: funções de data e hora

1. Introdução a funções de data e hora: Tudo bem. Bem-vindos de volta, pessoal. Aqui é a Seção 7. Funções de data e hora. Uma das minhas seções favoritas entrega uma das seções mais importantes em todo este curso de treinamento, simplesmente devido ao fato de que muitas pessoas correm em uma série de problemas e dores de cabeça tentando trabalhar com datas e horários em excel. Eu sei que passei cerca de um ano meio que atrapalhando esses não entendendo bem o que estava acontecendo. Até que eu realmente me sentei para entender como o Excel está pensando em datas e como superaas datas de processamento. Até que eu realmente me sentei para entender como o Excel está pensando em datas e como supera E uma vez que eu consegui esse entendimento, na verdade é bem simples, e isso tornou tudo mais fácil quando se tratava de trabalhar com datas e horários. Então vamos começar com uma data Time 101 seção ou introdução. Vamos falar sobre o valor da data, que é uma espécie de peça chave aqui sobre por que ele vai ser o seu novo melhor amigo quando se trata de trabalhar com datas e horários e formatação de data de conversa Excel e, em seguida, a opção de preenchimento da Siri, que é um atalho agradável para criar datas sequenciais que estamos indo hoje e agora , que são funções voláteis que retornam a data atual e a hora exata que vamos falar ano, mês, dia, hora, minuto e segundo, que concordam com maneiras de categorizar seus dados de data. Falaremos no fim do mês e o seu doido vai para os dias úteis da rede de dias úteis. Essas dicas para mostrar quantos dias de trabalho caem entre dois encontros? Que data se segue a um certo número de dias úteis a partir de hoje, assim por diante e assim por diante? E então vamos terminar com a diferença de data, que é outra ferramenta para calcular diferenças entre duas datas e, em seguida ,terminar com , , uh, mãos muito úteis na demonstração, onde eu vou mostrar a vocês como crie sua própria ferramenta de ritmo de orçamento, que você pode personalizar e transformar em qualquer coisa que quiser. Dois arquivos para download tem o nosso Excel para analistas funções de data e hora. PDF e sua seção sete data funções de tempo Excel File, que estará caminhando através de como nos movemos através das palestras. Então vá em frente e baixe esses dois arquivos. Você poderia imprimi-los se você escolher e, uh, vamos mergulhar direito 2. DATEVALUE: seu novo BFF: Tudo bem. Então, primeiro e acima de tudo, eu quero falar sobre algo chamado o valor da data. E se há uma palestra para ouvir ou um slide que você vai ler nesta seção inteira, este é o único para prestar atenção. E é porque o valor de data é a peça crítica que permite que o Excel processe datas e horas para cada data. Na verdade, cada momento no Excel tem um valor de data numérica subjacente ID associado. É assim que se destaca, capaz de fazer coisas como calcular a passagem do tempo ou aplicar funções estatísticas para datas sem esses valores de data datas datas seria tipo de apenas ser tratado como campos de texto. Você realmente não seria capaz de fazer muito. Ah, em termos de manipulação com essas datas. Então, a meia-noite de 1 de janeiro de 1900 é tratada como, eu acho, o início do tempo em excelência universo. Então, 1º de janeiro de 1900 tem um valor de data subjacente de um e, em seguida, todos os dias subsequentes além de 1º de janeiro de 1900 tem um valor de data um número maior, então 2 de janeiro de 1900 tem um valor de data de dois e assim por diante e assim por diante. Então, o Excel é realmente muito, muito inteligente sobre datas em, e então ele reconhece a maioria das datas de tipo e aplica automaticamente um formato de data comum. Na maioria dos casos, será mês barra dia ano, com quatro dígitos juntamente com seu valor de data associado. Agora, a maneira de verificar se sua entrada é reconhecida como uma data é clicar com o botão direito indo para vender , formatar e mudar para geral, e ele irá mostrar se há um valor de data subjacente. Então vou te mostrar um exemplo disso agora. Se eu pular em uma pasta de trabalho em branco aqui, vou digitar uma data à mão. Vou fazer o percenter de 1º de janeiro de 2015 e ter um clique direito em um indo para formatar células . Hum, como você pode ver, ele usa o campo de data padrão. Esta é a versão padrão Dave mês ano. Mas se eu mudar para a categoria geral, você pode ver que ele tem um valor subjacente de 42.000 e cinco porque é o dia que caiu 42.005 dias após 1 de janeiro de 1900 s. Então isso me diz que ok, Excel tem reconheceu a minha entrada como uma data que pode tratá-la como tal. Também é muito bom sobre a compreensão, meio que estranhamente formatado um. Então eu poderia dizer como, Oh um traço Owen, Dash 15 e você pode ver que ele mudou para a versão padrão porque ele sabia que eu estava tentando digitar uma data. Você pode realmente ficar um pouco estranho com ele. Sabe, eu gosto de testar para ver o quão estranho eu posso ficar. Veja até aquela versão onde eu digitei. Como você sabe, traço e combinação de traços e barras, ele ainda o reconheceu como uma data, e ainda aplicou esse valor de data a ele. Então o Excel é realmente muito inteligente quando se trata dessas coisas em. E essa é a razão principal por que são capazes de fazer tais manipulações interessantes e cálculos contra estados eso se acontecer de você tipo de data. Isso, exceto que não reconhece um exemplo comum que eu vi é se você digitar uma série de números como data mês dia. Então, para 1º de janeiro de 2015 novamente se você digitou 2015 0101 sem traços ou barras. Excel não reconheceria isso como uma data e, portanto, não se aplicaria no valor de data subjacente, e trataria como uma cadeia de texto ou, neste caso, valor. Então, se esse for o caso como se eu baixar alguns dados e o campo de data estiver em um formato muito estranho como esse, eu vou mostrar-lhe como usar ferramentas como o valor de data, valor de tempo para converter essas datas formatadas un em dois formatos que Excel Compreende. Então olhando para alguns exemplos aqui em baixo novamente, 1º de janeiro de 1900 é a data um, então o valor de data é igual a um 10 dias depois. No dia 11 em 1900 teria um valor de data de 11. 6 de fevereiro é 42.041 dias após 1º de janeiro de 1900 e esses valores de data não precisam ser números inteiros. Na verdade, 42 0 41,5 equivale ao meio-dia de 6 de fevereiro, enquanto 42 0 41,75 equivale a 6 horas, o que é 75% do caminho durante o dia. Então você pode ficar muito granular mesmo até o segundo em. É assim que você pode começar a trabalhar com momentos muito específicos no Excel, não apenas manter datas, então espero que isso ajude Uma vez que você entender esse conceito de valor de data, repente tudo se torna um pouco mais claro. Começamos a falar sobre manipular e aplicar funções a essas datas. 3. Formatação de datas e série de preenchimento: então eu só quero cobrir mais duas dicas e conceitos antes de mergulharmos nas próprias funções . A primeira é a formatação de data, portanto, quando você formatar datas e excel, você tem algumas opções. Você pode selecionar uma opção predefinida na categoria de data na caixa de diálogo Formato Células , que você pode acessar apenas clicando com o botão direito do mouse em uma célula escolhendo a formatação sul. Ou você tem a opção de criar seu próprio formato personalizado. Assim, os formatos predefinidos novamente vivem na linha de data ou na categoria de data da caixa de diálogo de células de formato . E há uma série de opções. Desculpe-se. É um pouco complicado de ler, mas você pode rolar para baixo e exibir sua data com várias variações diferentes . Você poderia mostrar apenas o dia e o mês, não o ano. Você pode mostrá-lo como um ano de dois dígitos versus quatro. Você poderia escrever o dia e deixar o ano como um número, como se houvesse dezenas e dezenas de maneiras de mostrá-lo e provavelmente tem o que você precisa. A outra opção é saltar para a categoria personalizada e escrever seus próprios formatos aqui, Então aqui os códigos que você pode usar. Isto é apenas uma amostra deles. A lista completa está disponível no suporte dot office dot com, mas isso só permite que você exiba uma data de outras maneiras adicionais. Então, por exemplo, se eu tiver um encontro em uma cela como 1º de janeiro de 2015 ah, e em vez de mostrar a data inteira, eu só quero mostrar que dia da semana é esse. Eu poderia formatar essa célula com um formato personalizado de de de, e ele irá exibir o valor como sábado ou sexta-feira ou quarta-feira. Eu não sei se o topo minha cabeça que dia da semana foi esse. Mas, um, importante notar que ele não vai mudar o valor subjacente em si. Isso só mudará a forma como você está exibindo. A segunda coisa que quero mostrar é Phil Siri, que é um ótimo atalho que o Excel usa para basicamente ajudar você a preencher uma série de datas sem ter que digitar manualmente em dias sequenciais ou meses ou anos. Então vamos dizer que eu escrevi 11 2015 e vender um aqui. O Excel o reconheceu como uma data. Se eu pegar o canto inferior direito da célula e arrastá-lo para baixo para um cinco por padrão. Excel vai assumir que eu quero mostrá-lo como ficar ao longo do dia tendências. Então basicamente adicionando um dia para cada linha. Se eu disser não, não quero fazer isso. Só quero uma cópia. 11 2015 e cada célula que eu posso clicar neste pequeno botão que aparece depois que você arrastou os valores e eu posso verificar cópias vende. Você verá que é padrão para preencher sério, que pressupõe que eu quero o dia passado, dia passado, dia após dia e então eu tenho outras opções. Aqui está bem, eu posso preencher apenas a formatação de Sally um. Eu poderia preenchê-lo sem a formatação ou se eu quisesse mostrar a sequência em um nível diferente do dia. Eu tenho essas opções também, então eu posso dizer Ok, vez de ir em incrementos de dias, vamos mostrá-lo em incrementos de meses, caso em que isso seria 1121314151 ou anos, caso em que seria 11 2015 2016 17 18 19 ou mesmo dias de semana para que eu possa dizer aumentar a data por um dia. Purcell, mas exclua fins de semana. Então, esta é uma maneira muito legal de criar janelas de datas rolantes ou automatizar uma grande parte do processo quando você está trabalhando com conjuntos de dados focados em data que estão constantemente mudando. Então tenha esses dois truques em mente, nós vamos usá-los um pouco em alguns dos próximos exercícios. 4. TODAY()/NOW(): Tudo bem, as primeiras funções de data e hora que eu quero compartilhar com vocês, nosso hoje e agora que são projetados para simplesmente retornar a data atual ou a hora exata atual . E a boa notícia é que estes ar talvez duas das funções mais simples no Excel e eles são tão simples porque eles realmente não se referem a outras células. Você só abre e fecha seus parênteses. Nem ponhas nada lá dentro. Chame por um dia e você está pronto para ir. Hum, importante notar que estas são funções voláteis, que significa que elas serão atualizadas sempre que você fizer qualquer alteração de planilha em qualquer pasta de trabalho aberta. Então, haverá mudanças constantes. Vamos saltar para o Excel e, uh, ir em frente e abrir a seção sete funções de data hora. Eu vou fazer uma rápida caminhada através, como você pode ver nas funções diurnas primeira guia. Eu tenho um espaço reservado para a data atual e hora atual, que nós vamos preencher em apenas um minuto usando hoje e agora nós também temos uma data de vencimento hipotética do projeto, que nós vamos chegar a mais tarde. E então é aqui que vamos praticar todas as funções que aprendemos nas próximas palestras. A segunda guia é uma ferramenta de ritmo de orçamento em branco. Esta é a demonstração que vamos percorrer no final da seção onde vamos trabalhar juntos e usar as funções de data e hora para construir uma ferramenta personalizada de ritmo de orçamento . E, em seguida, por último, mas não menos importante na terceira guia chamada dados de amostra. Só temos uma Siris de datas e despesas e este ar vai alimentar a ferramenta de ritmo orçamentário que construímos. Então, voltando às funções de data e hora no Tom 1. Vamos apenas preencher estas funções hoje e agora na célula C dois e C três. Então, para a data atual, só vou digitar igual a hoje. Abra, feche. Inserir ESO retorna a data atual, que é 18 de agosto de 2015 Hora atual. O mesmo processo é igual a Agora abrir fechar pressione enter. E como você pode ver, eles estão retornando a mesma coisa, exceto a hora atual ou a função agora é apenas um pouco mais granular que irá introduzir os componentes de hora, minuto e segundo que a data atual ou função de hoje não irá. Então lá vai você extremamente simples, mas este vai ser o nosso ponto de partida para praticar as funções nas próximas palestras 5. ANO/DIA/DIA e HORÁRIO/minuto/SEGUNDA: para que o Excel sempre calcule datas e horas com base em seus valores de cereais subjacentes precisos ou valores de data. Mas e se você precisar trabalhar com valores menos específicos? E daí se você não se importa com o dia específico? Apenas se preocupe com o mês ou o ano, ou até mesmo a hora ou o minuto. Se você precisar rolar seus dados de forma menos granular e visualizar as coisas em um nível mais alto, há um número de fórmulas de serialização que permitem que você faça isso,de modo que as mais comuns incluem ano, mês e dia e Se você precisar rolar seus dados de forma menos granular e visualizar as coisas em um nível mais alto, há um número de fórmulas de serialização que permitem que você faça isso, modo que as mais comuns incluem ano, hora, minuto e segundo. Basicamente, tudo o que fazem é extrair esses componentes individuais de uma determinada data. Então sintaxe sábia, eles são quase tão fáceis como fica escrito exatamente como você esperaria. Então ano é ano, mês, este mês, Dias, dia e assim por diante, e o único componente real da fórmula é a referência a uma célula contendo uma data ou hora. Então vamos saltar para o Excel e escrever alguns destes. O que vou fazer é preencher as células C 5 a C 10 para extrair ano, mês, dia, hora, minuto e segundo dos nossos campos de data e hora atuais. Então o ano atual será igual ao ano e o número de série ou referência que pode ser C 2 ou C 3 neste caso. Então eu começo com C para fechá-lo. Você verá que ele extrai 2015 porque essa é a data do dia atual. Eu também poderia mudar a referência. Vou te mostrar um atalho aqui em vez de entrar e mudar isso e digitar C 3. Obviamente, Obviamente, isso não seria muito difícil. Mas o que eu vou fazer em vez disso é pressionar F dois para mergulhar e, em seguida, pegar minha referência e arrastá-la para baixo para ver três pressionar enter. Isso fará exatamente a mesma coisa passando para o mês. Mesma história aqui, fazer o mês de C três dias de C três. Então isso está me dizendo que estamos no oitavo mês e no 18º dia do mês Agora, para o nosso minuto e segundo, eu tenho que me referir à função agora porque a função hoje não me dá esse nível de detalhe. Então, se eu tentar fazer o nosso de C 2, isso só vai me dar zero porque não há nosso especificado, hum saindo dessa fórmula. Então o que vamos fazer de novo é apenas mudar a referência. Arraste-o para baixo e agora ele está vendendo senhora na 12ª hora do dia por minuto novamente, consulte ver 3/30 minutos e finalmente, segundo do assento três é 16 e novamente. Como estas são funções voláteis, você pode vê-los mudar sempre que você fizer qualquer atualização de pasta de trabalho. Então, se eu digitar alguns valores aqui, ver que o segundo valor mudando ou se eu fui para a guia fórmulas, compactou este botão calcular agora e observou os segundos se marcarem. Então, lá vai você. É uma maneira muito simples, mas muito valiosa de extrair componentes específicos de uma data. 6. A função EOMONTH: Em seguida, temos o fim do mês ou o mês. Função, que calcula o último dia de um determinado mês, também pode ser usado para calcular, datas de início ou fim de meses anteriores ou futuros também. Então a sintaxe sábia, apenas dois componentes desta fórmula. Começa com a data de início. Pode ser uma data atual. Pode ser um encontro aleatório. Pode ser qualquer referência de célula a uma célula contendo uma data e, em seguida, o componente meses, que é o número de meses antes ou depois da data atual inicial. Então, se eu estiver interessado no último dia do mês atual, eu colocaria um zero por meses aqui. Se eu estiver interessado na última data do próximo mês, eu colocaria uma positiva. Se eu estivesse procurando a última data do mês anterior, colocaria uma negativa e assim por diante. Então vamos dizer que temos uma data de início ou data atual de 83 2015 em auto ver dois. Aqui, podemos usar a função EOE Month de várias maneiras. Se quisermos calcular o final do mês atual, reduzimos o mês Eo C dois vírgula zero. Hum, para calcular este início do mês, você tem que ficar um pouco inteligente, mas você pode usar a função EOE Month para fazer isso. Assim, por exemplo, o início do mês atual pode ser calculado usando Eo Mês ver para negativo um, que me dá a última data do mês anterior e, em seguida, basta adicionar um para obter a primeira data do atual mês. Então lembre-se que as datas realmente são apenas valores por causa desse valor de data subjacente. Então adicionando um a ele essencialmente apenas imposto em um dia. Então caso semelhante aqui para obter esta data de início do próximo mês enfraquecer, digamos, eo mês, Veja a zero comum para obter o último dia deste mês e, em seguida, adicionar um para obter a data de início do mês seguinte. Então é saltar para o Excel e realmente praticar este, hum, para o último dia do mês e vender ver 12. Tudo o que preciso fazer é acertar igual a um mês. E então minha referência de início, data ou venda a uma data pode ser C 2 ou C 3. Só vou escolher C para aqui e o que eu vou fazer é pressionar F quatro para corrigir essa referência, que lhe dará a opção de copiar e colar esta fórmula em outro lugar enquanto continuando a se referir ao assento. Então eu não quero que essa referência mude em mim vírgula para a próxima peça, que é meses. E neste caso, eu gostaria do último dia do mês atual. Então eu vou colocar um zero lá, fechar o parêntese e pressionar Enter. Então o resultado é 4 a 247 E se você prestou atenção às primeiras palestras, você provavelmente reconhecerá que este é um valor de data que ele está me mostrando. Isso significa que esta célula está formatada com o tipo de formatação geral. Então eu poderia fazer é clicar com o botão direito indo para formatar células, alterá-lo para data e, em seguida, selecionar qualquer opção que eu escolher aqui. Então eu vou usar o padrão, uh, mês, dia ano opção para nós. Ok. E lá vai você. 8 31 2015 Tenha em mente que dependendo de quando você estiver realmente fazendo esses exercícios, você verá valores e datas diferentes aqui do que eu. Não se preocupe com isso. os conceitos e fundamentos ainda devem ser aplicados. De qualquer forma, eu estou tão para obter o primeiro dia do mês, eu realmente vou copiar esta fórmula, colá-la para baixo, e agora lembrar o pequeno truque em vez de zero, que se aplica ao mês atual. Quero a última data do mês anterior. Então eu vou colocar um negativo, e então pulamos fora do parêntese e adicionamos mais um dia a ele, que me levará ao primeiro dia do mês atual, que é 81 2015. Agora, por último, mas não menos importante, primeiro dia do ano, este é um pouco mais complicado. Há algumas maneiras de fazê-lo. A primeira maneira é copiar e colar a mesma fórmula que acabei de escrever. Mas em vez de olhar um mês no passado, eu vou olhar oito meses no passado porque o mês atual é agosto ou oito. Então isso vai me dar o último dia de dezembro do ano passado, e então eu vou adicionar um, que deve retornar no primeiro dia do seu ano atual. Minha imprensa entra. Isso parecia funcionar. Então eu tenho 11 2015. Agora, quando você está fazendo isso em casa, você não pode subtrair oito meses, dependendo do mês que você está olhando. Assim, podemos modificar esta fórmula para fazê-la funcionar, independentemente da sua data e hora atual . E para fazer isso, vou substituir o menos oito aqui. Então o que eu preciso fazer é subtrair o número do mês atual, que requer que eu saiba qual é o número do mês e, em seguida, multiplicá-lo por um negativo para obter o componente do mês certo para esta fórmula. Então, na doença impressa, eu vou fazer negativo uma vez meses da data atual e, em seguida, fechar todos esses parênteses. Recapitulando o que fiz agora, o que estou dizendo é que quero o fim do mês. Somos Mike data atual ou ponto de partida de C 2. Mas o número de meses que eu quero olhar para trás no tempo é uma função do meu mês atual . Isto quer dizer que se estou no mês oito do ano, quero voltar no tempo oito meses e conseguir o último dia de oito meses atrás. Se eu estiver no décimo mês do ano. Eu quero saltar para trás e pegar os últimos oito de 10 meses atrás e assim por diante. E, novamente, esta última peça é apenas um ataque naquele dia extra para obter o primeiro do mês seguinte . Então, se pressionarmos Enter, lá vai você. Recebemos a mesma resposta 11 2015 e que agora retornará o primeiro dia do ano, independentemente da sua data e hora atuais. Então, como você pode ver, o mês E O é uma função realmente útil. Você pode usá-lo para fazer uma série de coisas muito criativas, e é uma ferramenta muito crítica para usuários do Excel trabalhando com datas e horários porque realmente não há maneira mais simples de fazê-lo. Dado o fato de que o número de dias por mês varia, esse tipo de automatiza um monte de trabalho manual tedioso que você teria que fazer de outra forma. Então lá vai você função E O mês 7. A função YEARFRAC: a próxima função que eu quero falar. Chama-se ano frack, que calcula a fração do ano representada pela diferença entre duas datas. Então, normalmente, isso é usado para comparar a primeira data de um determinado ano com a data atual para mostrar qual porcentagem do ano você está passando. Mas você pode fazer isso para qualquer dois encontros e olhar para ele. Basicamente, qualquer pessoa sabe que você escolher tão sábio sintaxe, três componentes para esta fórmula você tem sua data de início, sua data de término, e sua base base só permite que você especifique o tipo de contagem de dias para usar ou, em outras palavras, como você está definindo o número de dias em um ano. Zero é o padrão. Eu realmente recomendo colocar um aqui, que olha para o número real de dias no ano real que você está em com base em suas datas de início e fim. Então isso vai explicar coisas como, uh, uh, anos bissextos, que pode ter um dia adicional 366 contra 365 para o ano anterior ou o ano seguinte. Então aqui está um exemplo. Se você tem uma data de início em Selby 211 2015 e uma data de término na célula B três 2 28 2015 Aqui para exemplo, saídas no primeiro caso estamos usando a única base, que é real. E isso nos diz que em 28 de fevereiro de 2015 foram 15,9% do caminho ao longo do ano . E isso é até o ano real. 2015 se mudarmos a base para essa porcentagem muda ligeiramente para 16.1, porque agora é baseado em um 360 à base de ano. Então, pro tip aqui ano frack é realmente uma ótima ferramenta para coisas como cálculos de estimulação e projeção . Vamos dar uma olhada no Excel e basicamente vamos usar os dados que já inserimos aqui para calcular o percentual ao longo do ano da nossa data atual. Então eu vou fazer igual a você é frack, abrir o parêntese, nossa data de início vai usar o primeiro dia do ano, que nós calculamos na última palestra e, em seguida, a data de término e ver, e então minha base está indo para ser um. Quando fecho isso, recebo 63%. Então isso está me dizendo isso hoje, dia 18 de agosto. Estou 63% do caminho durante o ano. Então, lá vai você. Você é o Frack. 8. DIA DA DIA, DIA DOS DIA DOS dias de trabalho e WORKDAY: Certo, então vamos falar sobre mais três funções de encontro ou em dias de semana, dia de trabalho e dias de rede. Começando com um dia de semana realmente valioso. Ele mostra em que dia da semana uma determinada data cai, e há duas maneiras de fazer isso. O primeiro é apenas usar um formato de célula personalizado como falamos na palestra para, Então você pode usar três DS para obter o dia abreviado da semana ou quatro D para obter o dia inteiro da semana e novamente notar que isso não está realmente mudando o valor ou criando qualquer de novo valor. Está em Lee mudando como o valor da data original está sendo exibido para você. A segunda, que é a abordagem que eu recomendaria, está usando a função de dia da semana, que na verdade retorna um valor serial correspondente a um Dave específico naquela semana, seja um a sete ou zero a seis. Então essa sintaxe é bem simples. Você tem seu número de série primeiro, que é venda que contém uma data ou uma hora e, em seguida, seu tipo de devolução, que é opcional onde zero é seu padrão, que atribui um número um a um domingo, até o número sete, que é sábado. Hum, se você preferir chamar segunda-feira o início da semana e atribuir um para segunda-feira, você poderia colocar um aqui em vez de um zero ou em vez de deixá-lo em branco eso uma função realmente útil, especialmente quando se trata de fazendo coisas como as análises do Dia da Semana, onde queremos ver como desempenho ou os dados diferem, UH, terça-feira versus uma sexta-feira versus uma quarta-feira e assim por diante, um, dia de trabalho e dias de rede. Então, o dia de trabalho para ser totalmente honesto, é um pouco confuso. Basicamente retorna uma data que é um número especificado de dias antes ou depois de uma determinada data de início , excluindo fins de semana e feriados, se você optar por adicioná-los. Então a aplicação mais comum que eu posso pensar é dizer que data cai 50 dias úteis a partir de hoje. Assim que passar 50 dias úteis, que não inclui fins de semana, data em que vou aterrar e, por isso, o imposto sobre o pecado começa com a tua data de início, depois vai para o número de dias. Você pode colocar um valor positivo aqui se você quiser olhar para a frente no futuro ou valor negativo. Se quiser olhar para trás no passado. Aqueles ar todos os componentes necessários. Há um componente de feriados opcional que permite que você faça referência a uma lista de feriados e, essencialmente, excluirá aqueles do número de dias úteis que está calculando . Então esse é o Dia do Trabalho. Dia da Rede, que realmente acha muito mais útil e mais prático, mostra o número de dias úteis entre duas datas. Então você e colocar uma data de início na data final e uma referência opcional a uma lista de feriados, e ele irá dizer-lhe quantos dias úteis ou dias úteis caíram entre essas datas. Então, dois exemplos aqui eu tenho as mesmas referências até à data que eu mencionei na última palestra, que são 1º de janeiro de 2015 como nosso início, 28 de fevereiro de 2015 como nosso fim. Então, se dissermos dia de trabalho, ser muito comum 20 que dizer a partir da data de início de 1 de janeiro de 2015. Qual data cai 20 dias úteis depois disso, e ele retornará a data 29 de janeiro de 2015 se usarmos a função de dias de rede e definir B dois é a nossa data de início, e B três está lá terminado. Ele nos dirá isso entre 1 de janeiro e 28 de fevereiro de 2015. Havia 42 dias úteis, que é o número total de dias menos todos os fins de semana. Então vamos saltar para o Excel e ver tipo de como isso funciona na prática. Então, em F três, eu tenho uma célula de data de vencimento do projeto, e eu vou apenas inventar um encontro. No futuro, isso não significa nada. Então, digamos 9 de maio de 2017. Se for um grande projeto, vai levar muito tempo. Dias até fazer isso é realmente simples. Desde valores de data ar realmente apenas valores que eu posso executar qualquer operação normal em apenas como eu faria qualquer outro número, eu só vou dizer a data de vencimento menos a data atual agora retorna 6 30 Então tem 630 dias entre 18 de agosto de 2015 e maio 9 2017. Então esse é o total de dias até agora. Se eu quiser o número de dias de trabalho até fazer, obviamente, eu não vou passar meus fins trabalhando neste projeto. Posso usar dias de rede, então a data de início dos dias úteis líquidos é a data atual. 18ª data final é a data de vencimento. Então, se eu deixar o campo de feriados opcionais em branco. Feche os parênteses. Impress Center. Isso está me dizendo que bem, Eu tenho 630 dias totais até que é que eu só tenho 451 dias úteis até que ele é fazê-lo para a peça de feriados. O que eu não posso fazer é apenas digitar um número de feriados como cinco e dizer OK, excluir esses cinco feriados. Você vai apenas retornar o mesmo valor de 4 51 O que ele precisa aqui é uma referência real a uma lista de datas que definem os feriados que eu quero excluir eso o que eu posso fazer apenas por causa de um exemplo. Digamos 12 25 2015. Quero tirar o Natal e quero tirar o 1º de janeiro de 2016. E esses são os dois feriados entre a data de hoje e 9 de maio de 2017 que eu pretendo tirar e não trabalhar. Então o que, eu posso fazer isso dizer OK, em vez de colocar um cinco lá, eu vou me referir a idade 536 que é a minha lista de datas de férias e pressione enter, e agora você pode vê-lo atualizado para 449 então excluiu essas datas de feriados também. Note que se você tem um feriado que também cai em um fim de semana, ele já teria sido excluído aqui. Mas neste caso, ambas as datas são dias úteis, então foram contadas no total de 4 51. E então, quando eu adiciono essa referência, ela exclui aqueles dois e sai. Mova 449 dias úteis até o projeto ser vencido. último, mas não menos importante para o dia da semana novamente, eu poderia apenas clicar com o botão direito e ir para formatar células no campo de data real e dizer, Vamos apenas fazer um personalizado excluir esta linha de tipo e colocar Dee dee dee. Está bem, é terça-feira, mas quero continuar a mostrar a data no formato atual. E basta adicionar o dia da semana separadamente aqui. Então eu vou usar a função do dia da semana igual a semana, dia do número de série, que é F três. Vou deixar o tipo de retorno padrão, então eu recebo um três. E quando eu formatar os três que podem mudar isso para costume de de de e lá vai você me dá terça-feira. Então, 9 de maio de 2017 é terça-feira. Então lá vai você. Há dias úteis e dias de rede 9. A função DATEDIF: Então, a última função que eu quero falar nesta seção é chamada date def. E calcula o número de dias, meses ou anos entre duas datas dadas. Então, três componentes da sintaxe da fórmula começa com a data de início, em seguida, você e colocar uma data de término. A terceira peça é a unidade. Então isso é determinar como você deseja calcular a diferença entre os dois dias. Você pode definir um d aqui para calcular, pois o número de dias entre as datas M retornará o número de meses entre as datas. Por que retornaria o número de anos entre datas? Há também opções como M d y d ou por que eu m. Que irá calcular essas diferenças independentemente de meses ou independentemente de anos. Então, dois exemplos aqui novamente, estamos olhando para as mesmas datas de início e término que usamos nos dois exemplos anteriores. 1º de janeiro de 2015 Zehr começar 28 de fevereiro de 2015 é o nosso fim. Então, se definir a diferença de data entre a data de início dela e ser para na nossa data final e B três e disse que a unidade dois dias, ele vai retornar 58. Uma vez que eles são 58 dias entre esses estados e note que isso está funcionando como uma função de dias de rede exceto os dias de rede excluindo fins de semana. Isso está retornando todas as datas e também note que, uh, isso está fazendo exatamente a mesma coisa que apenas escrever a fórmula B três menos B dois. Então gorjeta profissional. Se você só precisa calcular o número de dias entre datas, basta usar subtração simples. Você não precisa usar uma data se funcionar para isso. Mas onde a data funcionou torna-se muito mais valiosa e onde não pode ser replicada . Usar operações simples é se você quiser calcular uma diferença em termos de meses ou anos, ou alguém e assim por diante. Então, se quisermos dizer diferença de data entre 1º de janeiro e 28 de fevereiro com a unidade M D, que é o número de dias entre datas ignorando meses e anos, ele vai dizer que eles são 27 dias entre o 28 e o primeiro. Independentemente disso, se eu estiver olhando para um mês diferente ou um ano diferente, vamos dar uma olhada no Excel e praticar isso. Então, para revisitar este projeto data de vencimento que tínhamos escrito na última palestra. O que eu posso fazer aqui é, se eu quiser calcular os meses restantes até o meu projeto ser vencido, eu posso digitar igual a data surda. E então eu não sei por que esse é o caso, mas o Excel não ajuda você com essa função. Só te deixa pendurado. Não sei se é um bug ou o quê, mas se dependermos da nossa memória, sabemos que o primeiro componente da data, def, def, é a data de início, que é a minha data atual. E então a data final será a data de vencimento. E então a última peça é a unidade, lembra? Nesse caso, preciso dos meses restantes, então vou colocar um “M” entre aspas. E isso não vai funcionar a menos que eu coloque o M entre aspas aqui. Então, mantenha isso em mente. E então quando eu apertei enter, isso me diz que eu tenho 20 meses restantes da data atual até a data em que meu projeto é devido e, em seguida, caso semelhante aqui, o que eu vou fazer em vez de escrever isso do zero, eu sou só vou corrigir as referências nas minhas funções originais de fixo o C dois e corrigir o F três. Copie essa fórmula, cole-a e mude o M para um Por quê, já que eu quero que os anos restantes. Então ele está me dizendo que eu tenho um ano restante até meu projeto terminar. Eu também poderia saltou de volta para vender F cinco, onde tinha calculado meus dias até fazer como apenas f três menos C dois e vamos ver se podemos obter a mesma resposta. 630 usando uma data funcionou. Então eu vou sair surdo C duas vírgula F três vírgula de entre aspas e lá vai você 6 30 então novamente duas maneiras de fazer a mesma coisa e eu não sei sobre você, mas eu prefiro apenas dizer F três menos C dois e chamar por dia. Mas data se pode ser uma ótima opção, especialmente se você quiser agregar a diferença entre duas datas no níveldo mês ou ano nível 10. MOSTRA DE PROJETOS: como criar uma ferramenta de aprovação de orçamento de amostras: Tudo bem. Bem-vindos ao fim da Seção 7. Parabéns por ter conseguido passar. Falamos sobre uma tonelada de funções de data e hora diferentes e úteis, e agora é hora de colocar algumas delas em prática. Então o que nós vamos fazer é tipo de passo a passo através deste projeto que incorpora um monte de diferentes funções de tempo de namoro em conjunto com algumas das funções que aprendemos anteriormente em nosso treinamento. Então vamos fazer isso. A primeira coisa que precisamos é da data atual e ver e ao contrário do que você pode estar pensando, eu realmente não quero usar a função de hoje aqui. primeira coisa que precisamos é da data atual e ver e ao contrário do que você pode estar pensando, um Éumpouco enganador, mas não quero voltar em que dia é hoje. Quero devolver o último dia do meu conjunto de dados. Então, se você se lembrar desta amostra de dados tem despesa declarada por dia indo para baixo até 16 de maio. Então, se eu não tiver dados de despesas depois disso e eu digitar hoje, que é em agosto, essa ferramenta não será muito útil. Então, em vez disso, vou usar uma função máxima. Eu só quero retornar a data máxima da coluna A dos meus dados de amostra, Ted, Agora vamos retornar 5 16 O outro benefício de fazer isso é que quando você está trabalhando neste exercício em sua própria casa e você usar a função de hoje, não vai divertir-se com dados de amostra e não vai realmente fazer muito sentido. Então, por enquanto, basta usar a função do Mac. E se você acabar criando um desses ou personalizando um deles para seu próprio uso, você pode alterá-lo como quiser. Eu sou tão início Data vai ser o primeiro dia do mês atual em novamente atual dados mais recentes em nosso conjunto de dados. Então eu vou usar a função Month EOE aqui. Data de início. Vai ser C 2 e lembre-se que para obter a primeira data do mês atual, eu preciso pegar a última data do mês anterior então menos um mais um dia, isso me dá 51 2015 que é exatamente o que eu quero agora por cento do mês. Este parece que deve ser muito fácil, mas na verdade é um pouco mais complicado do que você imagina, porque não há realmente nenhuma função mês-frack . Uh, você sabe, ano. Frack é realmente útil porque você poderia apenas dizer Diga-me a porcentagem através do ano que eu sou Não há um equivalente para frack mês e os desafios que cada mês tem um número diferente de dias. Então eu sei que estou 15 dias do caminho até maio. Mas eu divido isso por 30 dias ou 31 ou 29? Então o que precisamos é de uma pequena tabela útil, que eu convenientemente adicionei aqui nas células B 24 a C 35 que me diz o número de dias em cada mês com base no número do mês s para que eu possa usar isso para calcular minha porcentagem do mês é igual à minha data atual menos a minha data de início. E eles cercam essa paz entre parênteses. Esse é o número de dias ao longo do mês que atualmente estou. E eu vou dividir isso pelo número total de dias no mês. Para conseguir isso, preciso usar uma função de pesquisa V e o valor de pesquisa será o mês atual, que é o mês da minha data atual. Então mês de C dois irá apenas retornar cinco e cinco é o valor de pesquisa que eu estou procurando nesta tabela. Tudo bem, então ele vai procurar por cinco, e então eu retornaria o valor na segunda coluna sobre. É onde meu número de dias vive. E então vai ser uma correspondência exata. Então termine com um zero perto. O parêntese e o hit enter agora retornaram 48,4% me dizendo que no dia 16 de maio, eu estava 48,4% do caminho ao longo do mês. , E novamente, só para recapitular o que esta fórmula está fazendo, é calcular. O número de dias passaram até maio dividido pelo número de dias em maio. E ele descobre que, procurando o mês da minha data atual para baixo nesta matriz de tabela e retornando o valor na segunda coluna sobre. Então essa é a peça. A próxima peça é tudo sobre meu orçamento mensal e minhas despesas mensais. Então, o orçamento mensal é uma figura inserida pelo usuário, e isso é o que você quiser. Digamos que o seu orçamento mensal seja $2500. Isso é apenas uma espécie de pedra agora para calcular as despesas mensais. Há um passo que precisamos tomar porque nossos dados de despesas estão no nível diário e estamos procurando acumulá-los no nível mensal. Então precisamos criar uma nova coluna de coluna, um mês chamado, que rola esses dados no nível do mês. Então vai ser igual ao mês do encontro, também, e depois aplicar, e isso vai me mostrar. OK, mês número um para todas as datas de janeiro para 3 de fevereiro para março para abril e cinco a maio. E agora que eu tenho essa dimensão adicional, hum, eu posso usar uma fórmula de ifs para enrolar meus totais de despesas no nível do mês, não apenas no nível do dia. Então, se você se lembrar de alguns ifs da seção de funções estatísticas, vamos apenas começar com alguns ifs, abrir parênteses, e então o intervalo algum é basicamente onde os dados vivem. Que estou tentando agregar é a coluna de Despesas. Veja meus dados de amostra 10 e, em seguida, o intervalo de critérios é basicamente o número do mês, porque esse é o critério que ele precisa para atender a agregar esses totais de despesas para que o intervalo de critérios está chamando A e meus critérios é o mês da data atual. Lembrem-se do que vou precisar é de uma função que devolva o número cinco aqui. E assim será apenas meses de orçamento mensal ferramenta de ritmo célula C dois e, em seguida, fechar esses dois parênteses. E lá vai você. Então, quando eu apertei Enter ele retorna. 16 03 O que está me dizendo que todos os dados de despesas no mês de maio estão totalizando 2 $1603. Eu posso verificar isso rolando para baixo para os dados de maio e apenas olhando para o sol 16 03 Então isso está funcionando corretamente. Se eu quisesse tornar essas fórmulas um pouco mais fáceis, o que eu poderia fazer é apenas em outra cela. Se eu quisesse tornar essas fórmulas um pouco mais fáceis, Eu poderia apenas escrever essa função mês para retornar os cinco e, em seguida, em meus alguns ifs, vez de aninhar a função mês lá, eu posso apenas basicamente referir-se a essa célula onde já os colocaram em função e obter o mesmo resposta. Então, algumas maneiras diferentes de fazer isso. Depende especificamente do que você está procurando fazer agora. Percentual do orçamento vai ser o que gastei dividido pelo que meu orçamento é tão 64,1%. Hum, agora, aqui você vai ver que eu adicionei a estrutura para gráfico. Estou tão clique nisso, e vamos clicar com o botão direito para selecionar os dados. E eu sei que esse treinamento não está em gráficos e gráficos, então peço desculpas se isso for difícil de seguir, hum, eu vou colocar para fora, claro, especificamente cobrindo gráficos e gráficos em um futuro próximo. Então fique atento para isso. Mas enquanto isso, tente seguir passo a passo enquanto construímos isso. Então, vou adicionar uma Siri. O nome da Siri será chamado por cento do mês nos valores de uma Siri será apenas a célula C por apenas me dizer a porcentagem do mês atingiu. Ok, adicione mais um. Da Siri. O nome desta Siri vai ser por cento do orçamento, e os valores da Siri vai ser apenas vender quatro tudo bem, e então nós batemos. Está bem, outra vez. Como você pode ver, é um gráfico criado para mim. Isso mostra a porcentagem do orçamento mostrado em laranja e a porcentagem do mês mostrada em azul . Um, agora mais uma parte que vou fazer aqui nesta cela, eu tenho uma célula em cada um para o que eu vou fazer. É basicamente apenas calcular a diferença entre as duas porcentagens. Então vai ser igual a F para menos C quatro. Então isso está me dizendo que eu gastei 15,7% pontos acima do que eu estou andando para o mês. Então o que eu posso fazer agora apenas para adicionar alguns sinos e assobios é ir para formatação condicional regras de célula de destaque e dizer, Ok, se esse número é maior que zero, isso significa que a porcentagem do orçamento é maior que por cento do mês, o que significa que estamos a ultrapassar o ritmo, o que é uma coisa má. Vou formatar isso com um vermelho claro preenchido com texto vermelho escuro, que é uma das opções padrão e apertar OK e, em seguida, também em uma regra onde, se for menor que zero, significando que estavam em ritmo, vamos fazer preenchimento verde com texto verde escuro e bater OK, então agora se meu orçamento mensal foi $5000 agora vamos enviá-los sob ritmo por 16.3% pontos para que você possa tipo de jogar com ele, você sabe, você sabe, para ver como o gráfico atualiza e, em seguida, por último, mas não Pelo menos, se eu quisesse Teoh, você sabe, adicionar um pouco mais de talento a ele. Eu poderia adicionar um operador lógico aqui que diz, uh assim é igual. Se H dois é maior que zero, então eu quero que isso diga sobre o ritmo. Caso contrário, sob ritmo, Feche o Francis Então que só irá adicionar um pequeno rótulo lá sob o meu formatado condicionalmente . Mostre-me tudo bem, estou sem ritmo agora, mas se o meu orçamento era de 2500 agora, estou a exagerar. Então, lá vai você. É Thea, um ótimo exemplo de como combinar várias funções diferentes de várias categorias de fórmulas diferentes para criar algo que é realmente útil. A última coisa a falar é que esta ferramenta é ótima porque ele irá atualizar tipo de por conta própria como você adicionar mais dados. Então, digamos que mais uma semana se passa. Você sabe que pode arrastar isso para baixo, pois pode ver o dedo padrão preenchido da Siri adicionando dias para cada linha. Eu poderia arrastar minha fórmula mensal para baixo e então dizer OK gastou US $90 no 17º 114 nodia 18 e 75 no dia 19 e quando eu voltar para a minha ferramenta, como você pode ver, está atualizado. Eu poderia arrastar minha fórmula mensal para baixo e então dizer OK gastou US $90 no 17º 114 no 18 e 75 no dia 19 e quando eu voltar para a minha ferramenta, como você pode ver, Então agora a data atual é a 19ª desde duas funções max. Tenho 58,1% e 75,3%. Eles são meu fator de orçamento naqueles dias adicionais que acabei de adicionar. Então, passamos algum tempo construindo o front-end, mas agora é praticamente automatizado para usar muito facilmente avançar. Então isso encerra as funções do tempo de namoro. Espero que tenha achado útil. Fique atento para a próxima seção, que é regras de formatação baseadas em fórmulas. 11. OQUE DIA: Funções de data e hora: Tudo bem. Parabéns por encerrar a Seção 7. Funções de data e hora. Se quiser praticar mais. Tenho algumas boas demonstrações para você no arquivo de exercícios de lição de casa do Excel. Então vá em frente e abra. Vá para a guia de funções de data e hora. E aqui temos sete passos. Essencialmente, você vai praticar usando todas as funções que abordamos neste capítulo hoje. Agora dia de trabalho, dia de rede, fim de mês no Phil Siri. Então vá em frente e dê que um arquivo de resposta de tiro está disponível no curso. O recurso é ou apenas mande uma mensagem se precisar de apoio. Boa sorte.