Total store hours not adding up

[Vcoppens]

Hi,
It should be plain and simple but the time is not adding correctly any time a store has also Saturday hours.
Mon-Thu 9:00 AM - 4:00 PM
Fri 9:00 AM - 6:00 PM
Sat 9:00 AM - 1:00 PM

The hours are formatted to custom h:mm AM/PM

The formula adds correctly when calculating Mon thru Fri (total 37 hours) but jinx when including Sat (formula results in 65 hours and should be 4

=sum(((D1-C1)*24)+((F1-E1)*24)+(H1-G1)*24)

Any help is much appreciated!

 

[queuesmef]

So without your concrete example, it's hard to 'see' what you actually type in cells. The problem is using hours. A) Using decimals would simplify things (Ex. 8:15am = 8.25; 4:45pm = 16.75) thus making the math simpler. B) I like using military time (24 hour clock ~ example 4:45p m = 16:45 C) Take difference for cells, Multiply by 24 hours (Note: the calculation cell format as NUMBER not date).
.....I'm sorry, I created a file, but can not find how to upload it.

 

[Eric Golf]

Hi,

Similarly, without seeing the actual layout & cell contents it is hard to tell, however I think you may be missing a couple of brackets in your formula:-

=sum(((D1-C1)*24)+((F1-E1)*24)+((H1-G1)*24))

Hope this helps,

Eric

 

[Vcoppens]

Hi Eric and queuesme,

thank you for offering your help.
I am trying to present this via an IPhone as I cannot access any external sites from my work computer.
Basically:
Column A: names of state
Column B: street name of store
Column C: opening hour Mon through Thu
Column D: closing hour Mon through Thu
Column E: opening hour Fri
Column F: closing hour Fri
Column G: opening hour Sat
Column H: closing hour Sat

I am in favor of military time too but the people who want to look at this want the format of hours in AM & PM.

Eric, I tried the missing ( ) but it doesn’t work. The formula works perfect to add hours from Monday thru Friday and when there are no opening hours in Saturday (this leaving cells empty).

The problem starts when there are hours in column G and H.

any suggestions are welcome.

thank you very much to both of you for trying.

 

[Vcoppens]

I found a solution!

when I go back in the Saturday hours and overwrite the hours by typing for example 8 a and 1 p it solved my problem.