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

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:

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:

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:

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:

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:

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:

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:

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

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.

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:

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:

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!

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.

Leia mais... Download grátis... Compra...

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!

Leia mais... Download grátis... Compra...

48 Comments
Login

Sort by Newest
- Best
- Popular
- Newest
- Oldest

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
- Reply
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
- Reply
- 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.
  
  Reply
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!
- Reply
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).
- Reply
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?
- Reply
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.
- Reply
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"))))
- Reply
- To post as a guest, your comment is unpublished.
  DJ · 5 months ago
  
  I just get "FALSE" as an answer using this formula.
  
  Reply
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.
- Reply
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.
- Reply
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?
- Reply
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!
- Reply
- 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 :-)
  
  Reply
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
- Reply
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"),
- Reply
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
- Reply
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
- Reply
- 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
  
  Reply
- To post as a guest, your comment is unpublished.
  Alex Hart · 2 years ago
  
  then it is not an end time :)
  
  Reply
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"),
- Reply
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?
- Reply
To post as a guest, your comment is unpublished.
narender kumar · 3 years ago
what if weekend is only sunday
- Reply
- 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
  
  Reply
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!
- Reply
- 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!
  
  Reply
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?
- Reply
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"
- Reply
- 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)
  
  Reply
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?
- Reply
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..
- Reply
- 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 !
  
  Reply
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.
- Reply
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??
- Reply
- 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
  
  Reply
  
  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.
  
  Reply
  
  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 )
  
  Reply
  
  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.
  
  Reply
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.
- Reply
- 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
  
  Reply
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.
- Reply
- To post as a guest, your comment is unpublished.
  Z. · 3 years ago
  
  I have the same problem.
  Did you get it solved?
  
  Reply
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.
- Reply
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).
- Reply
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
- Reply
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
- Reply
- 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
  
  Reply
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
- Reply

Acesse sua conta

Resetar a senha