Note: The other languages of the website are Google-translated. Back to English
Log in  \/ 
x
or
x
Inscreva-se  \/ 
x

or

Como calcular as horas de trabalho líquidas entre duas datas, excluindo fins de semana ou feriados no Excel?

Em muitas empresas, os funcionários são pagos por horário de trabalho. Calcular as horas líquidas de trabalho em um dia é fácil, mas que tal calcular as horas líquidas em um intervalo de datas? Para isso, este artigo apresenta as fórmulas de cálculo das horas líquidas de trabalho entre duas datas excluindo fins de semana e feriados no Excel.

Calcular dia de trabalho exclui fins de semana

Calcular horas de trabalho, excluindo fins de semana / feriados


seta azul bolha direita Calcular dia de trabalho exclui fins de semana

Nesta parte, apresento a fórmula para calcular o dia útil entre dois horários, excluindo fins de semana.

1. Selecione duas células nas quais você irá inserir a data e hora de início e hora de término, e clique com o botão direito para selecionar formatar células formar o menu de contexto. Veja a imagem:
doc horas de trabalho líquidas 1

2. No formatar células diálogo, clique em Número guia, e selecione personalizadas forme o Categoria lista e entre m / d / aaaa h: mm no Tipo caixa de texto na seção certa. Veja a imagem:
doc horas de trabalho líquidas 2

3. Clique OK. E insira a hora da data de início e a hora da data de término nas duas células separadamente. Veja a imagem:
doc horas de trabalho líquidas 3

4. Na célula ao lado dessas duas células, C13, por exemplo, insira esta fórmula =NETWORKDAYS(A13,B13)-1-MOD(A13,1)+MOD(B13,1)e pressione Entrar chave, e você obterá o resultado com formato personalizado, selecione a célula de resultado e clique Home guia e vá para o Formato de número lista para selecionar Geral para formatá-lo como o formato correto. Veja a imagem:
doc horas de trabalho líquidas 4


seta azul bolha direita Calcular horas de trabalho, excluindo fins de semana / feriados

Se você deseja calcular as horas de trabalho líquidas excluindo fins de semana ou feriados, você pode fazer o seguinte:

Calcular horas de trabalho líquidas excluindo fins de semana

1. Selecione duas células e formate-as como formato personalizado m / d / aaaa h: mm e insira a data e hora de início e a hora de término. Veja a imagem:
doc horas de trabalho líquidas 5

doc horas de trabalho líquidas 6

2. E ao lado da célula, C2 por exemplo, insira esta fórmula,
=(NETWORKDAYS(A2,B2)-1)*("17:30"-"8:30")+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),"17:30","8:30"),"17:30")-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),"17:30","8:30"),
imprensa Entrar chave, então você obtém uma string numérica. Veja a imagem:
doc horas de trabalho líquidas 7

3. Clique com o botão direito na string numérica e clique em formatar células do menu de contexto, e em formatar células diálogo, selecione personalizadas formulário Categoria lista sob Numbeguia r e insira esta [Hmm na caixa de texto Tipo. Veja a imagem:
doc horas de trabalho líquidas 8

4. Clique OK. Agora, são contadas as horas de trabalho líquidas entre duas datas, excluindo fins de semana.
doc horas de trabalho líquidas 9

Ponta: Na fórmula, A2 é a hora da data de início, B2 é a hora da data de término, 8:30 e 17:30 são a hora de início geral e a hora de término de cada dia. Você pode alterá-los conforme necessário.

Calcule as horas de trabalho líquidas, excluindo fins de semana e feriados

1. Como acima, selecione duas células e formate-as como formato personalizado m / d / aaaa h: mme insira a hora da data de início e a hora da data de término.
doc horas de trabalho líquidas 10

2. Selecione uma célula em branco e insira a data do feriado nela, aqui eu tenho 3 feriados e os digito separadamente em H1: H3. Veja a imagem:
doc horas de trabalho líquidas 11

3. Selecione uma célula em branco que colocará o resultado contado, C2 por exemplo,
=(NETWORKDAYS.INTL(A2,B2,11,H$1:H$3)-1)*("17:30"-"8:30")+IF(NETWORKDAYS.INTL(B2,B2,11,H$1:H$3),MEDIAN(MOD(B2,1),"8:30","17:30"),"17:30")-MEDIAN(NETWORKDAYS.INTL(A2,A2,11,H$1:H$3)*MOD(A2,1),"8:30","17:30"),
e imprensa Entrar , você obterá uma string numérica e a formatará como formato personalizado [Hmm. Veja a imagem:
doc horas de trabalho líquidas 12

Ponta: Na fórmula, A2 é a hora da data de início, B2 é a hora da data de término, 8:30 e 17:30 são a hora de início e a hora de término gerais de cada dia, H1: H3 são as células de feriados, você pode alterá-las como você precisa.

Adicione facilmente dias / anos / mês / horas / minutos / segundos a uma data e hora no Excel

Suponha que você tenha dados de formato de data e hora em uma célula e agora precise adicionar um número de dias, anos, meses, horas, minutos ou segundos a essa data. Normalmente, usar fórmula é o primeiro método para todos os usuários do Excel, mas é difícil lembrar todas as fórmulas. Com Kutools for Excel'S Auxiliar de data e hora utilitário, você pode facilmente adicionar dias, anos, meses ou horas, minutos ou segundos a uma data e hora, além disso, você pode calcular a diferença de data ou a idade com base em um determinado aniversário sem se lembrar da fórmula. Clique para experimentar gratuitamente em 30 dias!
doc adicionar hora minuto segundo
 
Kutools para Excel: com mais de 300 suplementos úteis do Excel, grátis para testar sem limitação em 30 dias.

As melhores ferramentas de produtividade para escritório

O Kutools for Excel resolve a maioria dos seus problemas e aumenta sua produtividade em 80%

  • armadilha para peixes: Insira rapidamente fórmulas complexas, gráficos e qualquer coisa que você tenha usado antes; Criptografar células com senha; Criar lista de discussão e enviar emails ...
  • Super Formula Bar (edite facilmente várias linhas de texto e fórmula); Layout de leitura (ler e editar facilmente um grande número de células); Colar na faixa filtrada...
  • Mesclar células / linhas / colunas sem perder dados; Dividir o conteúdo das células; Combinar linhas / colunas duplicadas... Evite células duplicadas; Comparar intervalos...
  • Selecione Duplicado ou Único Linhas; Selecione linhas em branco (todas as células estão vazias); Super Find e Fuzzy Find em muitos livros; Seleção aleatória ...
  • Cópia exata Várias células sem alterar a referência da fórmula; Criação automática de referências para várias folhas; Inserir marcadores, Caixas de seleção e mais ...
  • Extrair Texto, Adicionar texto, remover por posição, Remover Espaço; Criar e imprimir subtotais de paginação; Converter entre conteúdo de células e comentários...
  • Super Filtro (salvar e aplicar esquemas de filtro a outras planilhas); Classificação Avançada por mês / semana / dia, frequência e mais; Filtro Especial por negrito, itálico ...
  • Combine pastas de trabalho e planilhas; Mesclar tabelas com base em colunas-chave; Divida os dados em várias folhas; Conversão em lote de xls, xlsx e PDF...
  • Mais de 300 recursos poderosos. Suporta Office / Excel 2007-2019 e 365. Suporta todos os idiomas. Fácil implantação em sua empresa ou organização. Teste gratuito de 30 dias com recursos completos. Garantia de devolução do dinheiro em 60 dias.
guia kte 201905

Guia do Office traz interface com guias para o Office e torna seu trabalho muito mais fácil

  • Habilite a edição e leitura com guias em Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
  • Abra e crie vários documentos em novas guias da mesma janela, em vez de em novas janelas.
  • Aumenta sua produtividade em 50% e reduz centenas de cliques do mouse para você todos os dias!
officetab bottom
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    vishnu singh · 1 months ago
    I want to calculate next working day on the basis of date and time.
    After 17:00, date should show next working day exclude Sunday and holiday

    Example for dates and time

    28-08-2021 16:59
    28-08-2021 17:00
    28-08-2021 17:20
    29-08-2021 17:15

    Holiday list 

    22-Aug-21
    30-Aug-21
    02-Oct-21
  • To post as a guest, your comment is unpublished.
    Nicola Blue · 8 months ago
    HI Guys
    I have the formula working on Monday to friday
    however, we work 8-16:00 Monday to Thursday and 8-1 on a friday
    please help
    • To post as a guest, your comment is unpublished.
      Sagar · 8 months ago
      using "if". If the "day" is mon to thu, then one formula, if Friday, the same formula with changed time slot.
  • To post as a guest, your comment is unpublished.
    whammo18 · 9 months ago
    Super helpful post! I'm really close.

    I have the formula =(NETWORKDAYS(C2,D2)-1)*("17:00"-"9:00")+IF(NETWORKDAYS(D2,D2)MEDIAN(MOD(D2,1),"17:00","9:00"),"17:00")-MEDIAN(NETWORKDAYS(C2,C2)*MOD(C2,1),"17:00","9:00") but I need to find out how to take out an hour for lunch. I'm trying to figure out a calculation for SLA and need to account for an hour of lunch break from 9-5. How do I add 11am-12pm as an hour to exclude?

    Thanks in advance!
  • To post as a guest, your comment is unpublished.
    Gagan · 1 years ago
    can anyone help, i want to extract resolution time excluding night hours (e.g., 6PM to 6AM or 05PM to 8AM) including holidays (i.e., this industry works 24*7 hence does not want to exclude holidays/weekends).
  • To post as a guest, your comment is unpublished.
    Fiona · 1 years ago
    Whenever I do both calculations, my "work hours excluding weekends and holidays" comes out a lot higher than my "work hours excluding weekends" calculation. Anyone know why this might be?

    Start date: 1/9/2020 9:00
    End date: 28/5/2021 17:00
    *Work hours ex weekends:* 1552:00
    Formula:

    =(NETWORKDAYS(A4,B4)-1)*("17:00"-"9:00")+IF(NETWORKDAYS(B4,B4),MEDIAN(MOD(B4,1),"17:00","9:00"),"17:00")-MEDIAN(NETWORKDAYS(A4,A4)*MOD(A4,1),"17:00","9:00")

    Start date: 1/9/2020 9:00
    End date: 28/5/2021 17:00
    *Work hours ex weekends and holidays:* 1808:00

    Holidays:

    25/12/2020
    28/12/2020
    01/01/2021
    02/04/2021
    05/04/2021
    03/05/2021
    31/05/2021


    Formula:

    =(NETWORKDAYS.INTL(A7,B7,11,B$13:B$19)-1)*("17:00"-"9:00")+IF(NETWORKDAYS.INTL(B7,B7,11,B$13:B$19),MEDIAN(MOD(B7,1),"9:00","17:00"),"17:00")-MEDIAN(NETWORKDAYS.INTL(A7,A7,11,B$13:B$19)*MOD(A7,1),"9:00","17:00")

    Thoughts?



  • To post as a guest, your comment is unpublished.
    david udemezue · 1 years ago
    hello,
    if i have my start date, start time, end date and end time, how do i calculate for net hours used taking into consideration that work hours is 7hrs 30mins, and also weekends.
  • To post as a guest, your comment is unpublished.
    CL · 2 years ago
    PLEASE NOTE that the work day and time formula does NOT WORK if the time of the start value (i.e. 11am) is greater than the time in the second value (i.e. 9am). It will give a negative value which will deduct these hours from the work time. To capture that properly, you need to add another condition to the formula. IF(IF(NETWORKDAYS.INTL(B2,B2,11,H$1:H$3),MEDIAN(MOD(B2,1),"8:30","17:30"),"17:30")-MEDIAN(NETWORKDAYS.INTL(A2,A2,11,H$1:H$3)*MOD(A2,1),"8:30","17:30") <0, TIME("17:30"-"8:30",0,0)+(IF(NETWORKDAYS.INTL(B2,B2,11,H$1:H$3),MEDIAN(MOD(B2,1),"8:30","17:30"),"17:30")-MEDIAN(NETWORKDAYS.INTL(A2,A2,11,H$1:H$3)*MOD(A2,1),"8:30","17:30"))),IF(NETWORKDAYS.INTL(B2,B2,11,H$1:H$3),MEDIAN(MOD(B2,1),"8:30","17:30"),"17:30")-(MEDIAN(NETWORKDAYS.INTL(A2,A2,11,H$1:H$3)*MOD(A2,1),"8:30","17:30"))))
    • To post as a guest, your comment is unpublished.
      DJ · 5 months ago
      I just get "FALSE" as an answer using this formula.
  • To post as a guest, your comment is unpublished.
    Chad · 2 years ago
    I am getting incorrect values when I enter in this equation, trying to look at open and close times, same as work hours.
  • To post as a guest, your comment is unpublished.
    karthik · 2 years ago
    Ho to calculate time difference between date and time from below date and time excluding weekends.
    Start date 12/31/2022 9:04 End Date 1/3/2023 8:07 in HH:MM:SS format.
  • To post as a guest, your comment is unpublished.
    George · 2 years ago
    Works greet in excel.. trying to replicated the same logic in Power BI but having issues. Any body has an idea?
  • To post as a guest, your comment is unpublished.
    BasilBase8 · 2 years ago
    I have edited your formula for work hours being 8am to 8pm. In my example, A2 is the start date time, B2 is the end date time, 8:00 and 20:00 are the start and end times each day, H$1:H$8 is the holiday cells.

    When the date range does not include a weekend, the formula works perfectly, but when the date range includes a weekend day, the result is always 12:00 greater than it should be

    The formula I'm using is =(NETWORKDAYS.INTL(A2,B2,11,H$1:H$8)-1)*("20:00"-"8:00")+IF(NETWORKDAYS.INTL(B2,B2,11,H$1:H$8),MEDIAN(MOD(B2,1),"8:00","20:00"),"20:00")-MEDIAN(NETWORKDAYS.INTL(A2,A2,11,H$1:H$8)*MOD(A2,1),"8:00","20:00")

    Here are 2 examples (there aren't any holidays (H$1:H$8) in these date ranges)
    Example 1
    Start - 2/6/2019 8:46
    End - 2/11/2019 8:40
    Value Using Formula - 47:54:00
    Correct Value - 35:54:00
    Difference - 12:00
    note date range has weekend date in it

    Example 2
    Start - 2/6/2019 19:26
    End - 2/8/2019 16:15
    Value Using Formula - 20:49
    Correct Value - 20:49
    Difference - 00:00
    note date range has no weekend dates in it

    Thanks for any help you can give!
    • To post as a guest, your comment is unpublished.
      RB · 2 years ago
      Had same issue, but with help of feedback 2 months ok of AydinB was able to fix it.

      Formula includes Saturday. Number 11 in
      Means Mon-Sat. Replace 11 with 1 for Mon-Fri :-)
  • To post as a guest, your comment is unpublished.
    blessing · 2 years ago
    hello i need help in calculating working hours in a day within some period of time. take for instance:

    work hours is 8am to 5pm. and i need to calculate this for 10 days. what formula can i use
  • To post as a guest, your comment is unpublished.
    AydinB · 2 years ago
    Formula includes Saturday. Number 11 in

    NETWORKDAYS.INTL(A2,B2,11,H$1:H$3)

    means Mon-Sat. Replace 11 with 1 for Mon-Fri :-)

    =(NETWORKDAYS.INTL(A2,B2,11,H$1:H$3)-1)*("17:30"-"8:30")+IF(NETWORKDAYS.INTL(B2,B2,11,H$1:H$3),MEDIAN(MOD(B2,1),"8:30","17:30"),"17:30")-MEDIAN(NETWORKDAYS.INTL(A2,A2,11,H$1:H$3)*MOD(A2,1),"8:30","17:30"),
  • To post as a guest, your comment is unpublished.
    J.M. · 2 years ago
    Hello

    I used your formula "=(NETWORKDAYS(A2,B2)-1)*("17:30"-"8:30")+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),"17:30","8:30"),"17:30")-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),"17:30","8:30")" works great

    But i get with this data negative Errors even using "1904 date system".

    The sample data is: 01.10.2018 15:10 / 03.10.2018 11:15 (date format t.m.jjjj hh:mm)

    The error is: data and times that are negative or too large show as #######

    Do you have an idea.

    Juan
  • To post as a guest, your comment is unpublished.
    Pedro · 3 years ago
    Hi everyone,


    There is an error on this formula:

    =(NETWORKDAYS.INTL(A2,B2,11,H$1:H$3)-1)*("17:30"-"8:30")+IF(NETWORKDAYS.INTL(B2,B2,11,H$1:H$3),MEDIAN(MOD(B2,1),"8:30","17:30"),"17:30")-MEDIAN(NETWORKDAYS.INTL(A2,A2,11,H$1:H$3)*MOD(A2,1),"8:30","17:30"),


    If the end time is greater than the start time, the formula give us the wrong value. We have to pay attention to that.


    But the formula was very helpful! Thanks
    • To post as a guest, your comment is unpublished.
      ranjith.deva@gmail.com · 2 years ago
      Hello Sir,
      Even I am finding the same error as you mentioned above, could you please share the answer if you have

      Deva
    • To post as a guest, your comment is unpublished.
      Alex Hart · 2 years ago
      then it is not an end time :)
  • To post as a guest, your comment is unpublished.
    Cherry · 3 years ago
    what is "11" in the formula --> (NETWORKDAYS.INTL(A2,B2,11,H$1:H$3)-1)*("17:30"-"8:30")+IF(NETWORKDAYS.INTL(B2,B2,11,H$1:H$3),MEDIAN(MOD(B2,1),"8:30","17:30"),"17:30")-MEDIAN(NETWORKDAYS.INTL(A2,A2,11,H$1:H$3)*MOD(A2,1),"8:30","17:30"),
  • To post as a guest, your comment is unpublished.
    Jack · 3 years ago
    Morning,

    I4 = Date Start MFG (9/14/18 8:00)
    J4:M4 = Estimated Hours of Work (28)
    N4 = Sum(J4:M4)/8 with the 8 representing the hours of work to calculate the # of days required
    O4 = MFG Lead-Time.. this is where I'm having an issue..

    What I want the spreadsheet to do is to tell me when the job is going to finish; more specifically, the time. However, I'm not sure how to write the formula so it only counts 7:00-17:00 and excludes 17:00-7:00.


    Right now, I have a 3.5 day LT beginning at 9/14/18 8:00 and the output is giving me 09/17/18 20:00. But I can't have a 20:00 because it's outside the standard hours of work. The desired result should be 09/17/18 12:00.

    Start Date: 09/14/18 8:00AM - 17:00PM is 1, 9/15/18 8:00AM - 17:00PM is 2, 9/16/18 8:00AM - 17:00PM is 3, 9/17/18 8:00AM - 12:00PM is 3.5.. any ideas?
  • To post as a guest, your comment is unpublished.
    narender kumar · 3 years ago
    what if weekend is only sunday
    • To post as a guest, your comment is unpublished.
      mahadev · 3 years ago
      use the formula =NETWORKDAYS.INTL you will get all the syntax over there for your query
  • To post as a guest, your comment is unpublished.
    Sebastian · 3 years ago
    Hi, I'm trying to find the formula that include the break time from 12pm to 1:30pm base on your working hours of 8am to 5pm. is it posted? thank you so much for your help, this Blog has helped me a lot!
    • To post as a guest, your comment is unpublished.
      Sandra · 3 years ago
      Any luck with this? I am also looking for a formula to subtract one hour per day, but not necessarily a set time. In my setting, the work day is 8-5, but only 8 hours are counted. Thanks!
  • To post as a guest, your comment is unpublished.
    Nawab · 3 years ago
    hi, i need to compare a login time if its in between a range of time in case if its fall time of range 1 then authorization time of same login is within specific time of range 1 and we have 3 to 4 range of different timings and its authorization respectively?
    can any one help?
  • To post as a guest, your comment is unpublished.
    kara m · 3 years ago
    This formula works to return the net working hours and minutes. How can I convert hours to days, based on an eight hour work day? For example, the result "0 day, 18 hours, 45 minutes" should be "2 days, 2 hours, 45 minutes"
    • To post as a guest, your comment is unpublished.
      deb · 3 years ago
      divide the hours by 8 instead of 24 as working hours is 8hrs for your team. so 18 hrs 45 mins/ 8 hrs= 2 days 2 hrs 45 mins (2 days = 2*8 = 16hrs)
  • To post as a guest, your comment is unpublished.
    Yesu · 3 years ago
    Hi, for me some values for start/end time falls on weekends or time outside the defined window. For these cells, the value is showing as 00:00:00. Is there a way to correct it?
  • To post as a guest, your comment is unpublished.
    jennycruz0711@gmail.com · 4 years ago
    Hi there, Thank you very much your formula it really helps me a lot with my work. but my challenge is how can you removed the break time from 12pm to 1:30pm base on your working hours of 8am to 5pm. It really means a lot to me if you will solved my problem. please help..
    • To post as a guest, your comment is unpublished.
      Sebastian · 3 years ago
      Hi, do you know where could I find the formula that includes the break you are mentioning? Thank you !
  • To post as a guest, your comment is unpublished.
    vishnu k · 4 years ago
    =(NETWORKDAYS(A2,B2)-1)*("17:30"-"8:30")+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),"17:30","8:30"),"17:30")-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),"17:30","8:30"),


    could you please explain how this works.
  • To post as a guest, your comment is unpublished.
    Rahul · 4 years ago
    =(NETWORKDAYS.INTL(A2,B2,11,H$1:H$3)-1)*("17:30"-"8:30")+IF(NETWORKDAYS.INTL(B2,B2,11,H$1:H$3),MEDIAN(MOD(B2,1),"8:30","17:30"),"17:30")-MEDIAN(NETWORKDAYS.INTL(A2,A2,11,H$1:H$3)*MOD(A2,1),"8:30","17:30"),

    What is number 11 in the above formula.??

    And also, How to write the holiday in formula if I have only one holiday in the month??
    • To post as a guest, your comment is unpublished.
      manohar · 4 years ago
      Number 11 (Sunday as weekend) refers to weekend number
      Weekend number Weekend days
      1 or omitted Saturday, Sunday
      2 Sunday, Monday
      3 Monday, Tuesday
      4 Tuesday, Wednesday
      5 Wednesday, Thursday
      6 Thursday, Friday
      7 Friday, Saturday
      11 Sunday only
      12 Monday only
      13 Tuesday only
      14 Wednesday only
      15 Thursday only
      16 Friday only
      17 Saturday only
      • To post as a guest, your comment is unpublished.
        appa · 3 years ago
        Hi,

        pls post how to calculate the time b/w two dates which includes weekends also.
      • To post as a guest, your comment is unpublished.
        appa · 3 years ago
        hi ,

        Could you please write the formula for calculating time b/w days which includes weekends ( sat n sun )
      • To post as a guest, your comment is unpublished.
        appa · 3 years ago
        Hi ,

        Can you please share the formula which calculates time including weekends(sat & Sun) also.
  • To post as a guest, your comment is unpublished.
    KMYounis · 4 years ago
    Hi,
    I have been trying to use this function to get minutes between two days excluding holidays and weekends. No matter what I always get 0 minutes in my answer. I will be more than happy to share my excel file if needed.

    Your help will be greatly appreciated.
    • To post as a guest, your comment is unpublished.
      Sunny · 4 years ago
      Thanks for ur message. For get minutes between two dates excluding holidays and weekends, you just need to change [h]:mm to [mm] in the last step
  • To post as a guest, your comment is unpublished.
    Claire · 4 years ago
    Thanks, in general this formula is working great for me thank you but how do I do it for time periods which exceed a month?

    My formula is this:

    =(NETWORKDAYS(L22,M22,BankHols17to21)-1)*("18:00"-"8:00")+IF(NETWORKDAYS(M22,M22,BankHols17to21),MEDIAN(MOD(M22,1),"18:00","8:00"),"18:00")-MEDIAN(NETWORKDAYS(L22,L22)*MOD(L22,1),"18:00","8:00")

    So these two dates show correctly as 11 working hours:

    18/05/2017 08:00 AM 19/05/17 09:00 AM

    But this one which is over a year difference between the dates shows ups 17 days 8 hours:

    18/05/17 00:00 28/05/18 09:00

    Thank you.
    • To post as a guest, your comment is unpublished.
      Z. · 3 years ago
      I have the same problem.
      Did you get it solved?
  • To post as a guest, your comment is unpublished.
    Ankit · 4 years ago
    Hi,

    Does this formula work in excel 2007. Because i tried to use the formula for calculating net working hours excluding weekend and holidays, but its not working. I am getting "#NAME?" as output. I am using it to calculate from 9 AM to 6 PM(18:00). Please help.
  • To post as a guest, your comment is unpublished.
    Ankit · 4 years ago
    Hi,

    Does this formula work in excel 2007. Because i tried to use the formula for calculating net working hours excluding weekend and holidays, but its not working. I am getting "#NAME?" as output. I am using it to calculate from 9 AM to 6 PM(18:00).
  • To post as a guest, your comment is unpublished.
    Adis Samardzic · 4 years ago
    Hello I need help on same topic

    In my case I have defined starting date and time (dd.mm.yy and hh.mm) and I have hours needed for some material to be produced (ex total 17 hours)

    My problem is how to subtract non working hours from total time needed third shift is not working from 0:00 AM - 7:00AM

    Please help
  • To post as a guest, your comment is unpublished.
    Steve · 4 years ago
    Really good info but can it be converted into minutes?

    Thanks
    Steve
    • To post as a guest, your comment is unpublished.
      Melinda · 4 years ago
      yes, format the result cell with custom format [mm] instead of [h]:mm
  • To post as a guest, your comment is unpublished.
    Deepinderpal · 4 years ago
    I have multiple dates I need to use to calculate the hrs spent on the iteration
    A - request received : 1/14/17 3:43
    B - request ended : 1/16/17 23:03
    C - clarification sent : 1/16/17 20:41
    D - clarification received : 1/16/17 22:38
    I need to find the difference B-A = E and then D-C = F
    now E-F should give me no. of hrs spend on this work which needs to be below 24 hrs