Ignore text in multiplication

[vincenzo1]

How do I get the formula to ignore the text in cell "D"?

DATE

Description

Qty Day

Daily Rate

.

Price

=Timesheet!B9

=Timesheet!F9

=IF(Timesheet!E9="04:00","0.5","1")&" "& "Day"

100

=(--[@[Qty Day]])*(--[@[Daily Rate]])

I keep getting "#VALUE".

Thanks

 

[StephenCrump]

Welcome to the Forum!

One way you could do this:

Qty Day: =IF(Timesheet!E9="04:00",0.5,1)
Custom Format as 0.0" day"

Price: =[@[Qty Day]]*[@[Daily Rate]]

 

[vincenzo1]

Welcome to the Forum!

One way you could do this:

Qty Day: =IF(Timesheet!E9="04:00",0.5,1)
Custom Format as 0.0" day"

Price: =[@[Qty Day]]*[@[Daily Rate]]

Welcome to the Forum!

One way you could do this:

Qty Day: =IF(Timesheet!E9="04:00",0.5,1)
Custom Format as 0.0" day"

Price: =[@[Qty Day]]*[@[Daily Rate]]

Stephen,

Thanks for the prompt reply.

DATE	Description	Qty Day	Daily Rate	.	Price
=Timesheet!B9	=Timesheet!F9	=IF(Timesheet!E9="04:00","0.5","1")&" "&"Day"	100		=[@[Qty Day]]*[@[Daily Rate]]

What I am trying to do is get this result

DATE	Description	Qty Day	Daily Rate	.	Price
9/09/2024	Job Description	0.5 Day	$ 100.00		$50

As soon as I add the "Day" to column "C" it can't calculate?

Cheers

 

[StephenCrump]

As soon as I add the "Day" to column "C" it can't calculate?

No, because you can't do arithmetic with a text value.

I have suggested that you use the formula: IF(Timesheet!E9="04:00",0.5,1) and custom format the cell as 0.0" day". That means it will contain the value 0.5, but display as 0.5 day.

Alternatively, don't bother showing "day" at all - as your column header Qty Day makes clear that the column contains numbers of days.

 

[vincenzo1]

Stephen,

I tried to custom format as 0.00 "Day", but the "Day" doesn't show? It shows if I use =IF(Timesheet!E9="04:00","0.5","1")&" "&"Day" but then won't calculate?

 

[StephenCrump]

You want a number, not a text value, so you need to use: =IF(Timesheet!E9="04:00",0.5,1)

Not: =IF(Timesheet!E9="04:00","0.5","1")

(And by the way, does Timesheet!E9 contain the text value "04:00" or the time 04:00, a number?

 

[vincenzo1]

The "04:00" is derived from =IFERROR(TEXT(D9-C9,"hh:mm")," ") on the Timesheet tab. I input the times

Date	Start Time (hh:mm)	Finish Time (hh:mm)	Total Hours (formula / electronic timesheet)	Comments
9/09/2024	16:00	20:00	04:00	Job description

Date	Start Time (hh:mm)	Finish Time (hh:mm)	Total Hours (formula / electronic timesheet)	Comments
45544	0.666666666666667	0.833333333333333	=IFERROR(TEXT(D9-C9,"hh:mm")," ")	Job description