Calculating FTE

[DawnRN]

I have a spreadsheet calculating hours worked, based on time of day start and time of day end, expressed as 08:00 or 10:00 or 12:00 hours. I would like to sum these columns and then divide by 80 to calculate FTE. I'm having trouble because the hours are expressed as time and not just a number. Any help is appreciated!

 

[RoryA]

If you sum and multiply that by 24, you will get a number of hours.

 

[DawnRN]

If you sum and multiply that by 24, you will get a number of hours.

Thank you! However, I'm still getting an error. What I have so far...
=24*(G7+J7+M7+P7+S7+V7+Y7+BC7+BF7+BI7+BL7+BO7+BR7+BU7)/80

 

[mole999]

format the cell as [h]:mm

 

[RoryA]

What error are you getting?

 

[DawnRN]

Value

 

[RoryA]

Then something in one of those cells isn't a number or date/time value.

 

[jtakw]

Hi,

Do you have formulas in the cells you're trying to SUM that might have put a Text value or "" (blank) in the cell?

Also, do you have Headers in the Columns you're trying to Sum?

 

[DawnRN]

Yes, I do have "" in the formulas. I think I've made some progress as I've formatted the cells I'm trying to sum as [H] so it is now a whole number 8, 10, or 12 rather than 8:00, 10:00, or 12:00 but still can sum the cells and then divide by 80 to calculate FTE for a two-week work period.

 

[RoryA]

Try:

=24*Sum(G7,J7,M7,P7,S7,V7,Y7,BC7,BF7,BI7,BL7,BO7,BR7,BU7)/80