I can't sum up hours anymore

[Michele317]

Hello

I'm doind an excel sheet in which I need to sum up hours.
For example, I have written in a cell "05:00" and in another "05:00". What I would expect by doing "=SUM(A1:A2)" is "10:00".

In the previous weeks, I already put in some calculation and they works perfectly: I have hours and I can sum them up.
Today, after some time I didn't open the workbook, I'm facing problem related to sum. I already tried to copy the format of other cells that are working and paste them into the ones that aren't, but nothing changed.
This doesn't work neither with hours put through a formula or put directly in the cell.

What am I missing?

Thank you in advance

 

[Scott Huish]

I'm going to assume you didn't actually put quote marks around your times, so did you format the cells as Text?

What happens if you use =A1+A2?

 

[Michele317]

I'm going to assume you didn't actually put quote marks around your times, so did you format the cells as Text?

What happens if you use =A1+A2?

The cells get the value through this formula: =IF(E4="Festivo";"";IF(OR(WEEKDAY(B4)=2;WEEKDAY(B4)=3;WEEKDAY(B4)=4;WEEKDAY(B4)=5);"08:00";"05:00")), where in B4 there are some dates, and it didn't sum up. I already put hours through a formula, and I can sum them up.
Anyway, I tried to just typing in a cell 05:00 and in another 05:00 (without quotes) and still the sum didn't work, neither using the SUM() function nor the + operator

 

[awoohaw]

what is the error you are getting?
Have you tried enter the values in the cell using the TIME() function?

=TIME(5,0,0)

 

[Michele317]

what is the error you are getting?
Have you tried enter the values in the cell using the TIME() function?

=TIME(5,0,0)

No error, just nothing sum up. I can see also on the bottom left that no sum shows up when I select multiple values

 

[awoohaw]

The cells get the value through this formula: =IF(E4="Festivo";"";IF(OR(WEEKDAY(B4)=2;WEEKDAY(B4)=3;WEEKDAY(B4)=4;WEEKDAY(B4)=5);"08:00";"05:00")), where in B4 there are some dates, and it didn't sum up. I already put hours through a formula, and I can sum them up.
Anyway, I tried to just typing in a cell 05:00 and in another 05:00 (without quotes) and still the sum didn't work, neither using the SUM() function nor the + operator

No error, just nothing sum up. I can see also on the bottom left that no sum shows up when I select multiple values

Well, your formula shows you are inputting those times as text, not a time value. You can use TIME(h,m,s) or h/24.
=TIME(8,0,0) or =8/24 for 8 am
=TIME(17,0,0) or =17/24 for 5 pm

and the OR() statement, are you trying to find a Monday-Friday date? If so, this is a slightly shorter formula to do that:

=WEEKDAY(B4,2)<=5

so your overall formula could be:

=IF(AND(E4="Festivo",WEEKDAY(B4,2)<=5),8,17)/24

(Change the 17 to 5, if you really want 5 AM).

 

[Michele317]

Well, your formula shows you are inputting those times as text, not a time value. You can use TIME(h,m,s) or h/24.
=TIME(8,0,0) or =8/24 for 8 am
=TIME(17,0,0) or =17/24 for 5 pm

I don't know, I have already done that in other formulas and it worked perfectly. I actually don't know why this time didn't work but anyway...
Anyway, thank you. I have tried by using the TIME() function and seems to work.

Thank you again

 

[awoohaw]

I don't know, I have already done that in other formulas and it worked perfectly. I actually don't know why this time didn't work but anyway...
Anyway, thank you. I have tried by using the TIME() function and seems to work.

Thank you again

OKay, you're welcome. Take a look at the update I made in the prior post. There is a shorter formula that you may like.

Best Wishes!