Help pls: I cannot format a column containing a formula to currency

Question

Level 1

5 points

Help pls: I cannot format a column containing a formula to currency

This is one of those ridiculously simple problems that takes up a disproportionate amount of time.

I am trying to use Numbers to keep a timesheet that calculates the amount to charge a client based on the hours worked on different tasks.

The spreadsheet has 3 columns: Task (Column B), Time (Column C), Amount £ (Column D)

The Amount column is formatted as currency. But when I put in a formula to automate the calculation it changes the formatting to Duration.

I use the formula C[row number]*30 (ie time spent x £30 hourly rate). The column D then changes to show the outcome as duration and I cannot get it to change to Currency. It seems to borrow the formatting from the Column C. Screenshot here:

Column C I have even formatted as text rather than duration and yet Numbers still insists that column D should relate to duration.

Can anyone help? This has already taken up far too much of my time and I'm not happy at being unable to resolve such a simple formatting issue. I thought I'd dip my toe into Numbers but if it can't cope with simple formatting then I'm about to head back to Excel.

MacBook Air 13″, macOS 11.6

Posted on Oct 6, 2021 11:30 AM

Reply

Answer 1

Best reply

SGIII

Level 8

35,546 points

Oct 6, 2021 2:08 PM in response to EB UK

When you input 18 hours or 3.25 hours, etc., in column B, Numbers sees the hours and guesses you mean Duration. So when you multiply that value by a number the result is Duration too.

The solution is to not type hours in column B. You could change the header for column B from Time to Hours.

Or, if you really want to display "hours" after the numbers down in the body cells, then you can create a Custom Format:

The actual value in the cell will be a number, but it will display with "hours" after it. Select other cells in column B and apply that Data Format just as you would any other format.

SG

Reply