EXCEL FORMULA QUERY

Daisy2513

Please help, I have spent the day trying to fix this formula and having no luck!

I have a roster and want to use it as a timesheet also, I want to calculate the weekly hours this is fine and works well until I add letters to code days off, this then results in #value! Leaving the days blank is not an option.

Can anyone advise on a formula that will ignore words contained in a cell and give me the total hours only.

Gulliver

Can you give an example of your data?

Aye Bosun

An easy way around it would be to use 'text to columns' function to split the number and text to differnt cells, you can then do your normal sum formula from there. You can always hide the 'text to columns' results so they are not seen on screen. You'll find 'text to column' under your 'data' tab

Daisy2513

Mon Tues Wed Thurs Fri Sat Sun
09:00 13:00 AL 09:00 13:00 DO 09:00 13:30 DO 10:00 14:00

I have the times in separate cells for subtraction purposes

Daisy2513

:-sorry that last message did not appear as it should

two rows with the following:
Row one : Days of the week
Row two: Times start and finish times in separate cells:

Mon
9:00 13:00
Tues
AL
Wed
9:00 13:00
Thurs
DO
Fri
9:00 13:00
Sat
DO
Sun
10:00 14:00

somesoldiers

Daisy2513 wrote: »

Mon Tues Wed Thurs Fri Sat Sun
09:00 13:00 AL 09:00 13:00 DO 09:00 13:30 DO 10:00 14:00

I have the times in separate cells for subtraction purposes

Is the time always to the right of the string? if so extract out the rightmost characters

marieholmfan

Daisy2513 wrote: »

Please help, I have spent the day trying to fix this formula and having no luck!

I have a roster and want to use it as a timesheet also, I want to calculate the weekly hours this is fine and works well until I add letters to code days off, this then results in #value! Leaving the days blank is not an option.

Can anyone advise on a formula that will ignore words contained in a cell and give me the total hours only.

Check the formatting. It might be easier to use a time format that shows AM and PM for the 14 hour time.

I get the sense that you're under pressure. Unfortunately don't have Excel at home but if you stick up a text sample I can have a look tomorrow.

TheChizler

I can't remember the exact syntax but something like SUMIF (<RANGE>, ">=0") could work.

homer911

You would be best posting a workbook with a small sample formula (exclude any personally identifiable data and blank the file properties)

marieholmfan

Daisy2513 wrote: »

Please help, I have spent the day trying to fix this formula and having no luck!

I have a roster and want to use it as a timesheet also, I want to calculate the weekly hours this is fine and works well until I add letters to code days off, this then results in #value! Leaving the days blank is not an option.

Can anyone advise on a formula that will ignore words contained in a cell and give me the total hours only.

Hi Daisy,
Can you post a small sample?

Have you tried formatting Excel recognises 19:00 as a time and this can be formatted as AM / PM
You can also use a weekday formula and the result can be formatted as ddd which shows Mon Tue etc.

Gulliver

Daisy2513 wrote: »

:-sorry that last message did not appear as it should

two rows with the following:
Row one : Days of the week
Row two: Times start and finish times in separate cells:

Mon
9:00 13:00
Tues
AL
Wed
9:00 13:00
Thurs
DO
Fri
9:00 13:00
Sat
DO
Sun
10:00 14:00

If I understand the structure correctly, you could use ISNUMBER and IF to check if the starting time is a number and then either return a calculation or a blank.

=IF(ISNUMBER(B2),C2-B2,"")

I've added a heading row and a total at the bottom for clarity.

TheChizler

I have a solution that works but the problem is I couldn't replicate your issue at all! My Excel just ignores text strings in SUMs. Have I got your table structure right? Days in row 1 times in row 2 etc...

Needlessly complicated formula attached.

Gulliver

Ok, based on TheChizler's fancy version (nice one, btw) I may have taken the format up wrong. Here's another attempt with the days in columns:



The formula is a bit clunky: =IF(ISNUMBER(B3),C3-B3,0)+IF(ISNUMBER(D3),E3-D3,0)+IF(ISNUMBER(F3),G3-F3,0)+IF(ISNUMBER(H3),I3-H3,0)+IF(ISNUMBER(J3),K3-J3,0)+IF(ISNUMBER(L3),M3-L3,0)+IF(ISNUMBER(N3),O3-N3,0)

The hours cells are formatted as [h]:mm:ss

namenotavailablE

Just seeing this now. Here's another non-array based formula (SUMPRODUCT is your friend in so many of these types of scenarios ) using TheChizler's sample file:

=SUMPRODUCT(--($B$2:$O$2="Finish"),B3:O3)-SUMPRODUCT(--($B$2:$O$2="Start"),B3:O3) for Aimee's time. Copy down as required.