Timesheet hours throwing a #Value!

[jltjr75]

Searching has given me several ideas but it seems to not work for me.
I'm getting a #Value! while trying to "Total Hours"
Also "Hrs Worked" should be hours and minutes (not AM PM) and I don't want it to show zeros or 12 for blank start and finish times.
I think once I get the Total hours worked out I can get the "$20hr * Total Hours =" and the "plus travel pay ="

My Google Test Sheet:

test-contractor

time sheet Total Hours Pay Period:,June 28, 2021 - Jul 8, 2021,41:15:00,$20hr * Total Hours =,$825.00,plus travel pay = ,$890.00 Date,Day,Start Time,End Time,Hrs Worked,Location (Site),Miles Driven,Travel Pay,Total,Notes June 28, 2021,Monday,5:45 AM,6:50 AM,1:05:00,job 2,48,$15.00 June 29, 2021,...

docs.google.com

 

[Toadstool]

Hi jltjr75

You need to make the sheet public.​

 

[jltjr75]

Hi jltjr75

You need to make the sheet public.​

test-contractor

time sheet Total Hours Pay Period:,June 28, 2021 - Jul 8, 2021,41:15:00,$20hr * Total Hours =,$825.00,plus travel pay = ,$890.00 Date,Day,Start Time,End Time,Hrs Worked,Location (Site),Miles Driven,Travel Pay,Total,Notes June 28, 2021,Monday,5:45 AM,6:50 AM,1:05:00,job 2,48,$15.00 June 29, 2021,...

docs.google.com

I logged in incognito because I thought I had already made it public. It seems to be.

 

[johnnyL]

I am not familiar with google docs/sheets so the best I could do is:

 

[jltjr75]

I am not familiar with google docs/sheets so the best I could do is:

I appreciate it JohnnyL. This is actually my first googlesheets attempt. I was able to get that far but when I multiplied it by 20/hr the numbers didn't add up as you can tell on the spreadsheet now. It totals up to $34.38

 

[Toadstool]

I see you've fixed the #VALUE error but remember time is measured as fractions of a day, so 41 hours and 15 minutes is actually held as 1.71875, or almost one and three quarter days.

To get the hourly rate you need to multiply by 24 so
=E2*20*24

 

[jltjr75]

I see you've fixed the #VALUE error but remember time is measured as fractions of a day, so 41 hours and 15 minutes is actually held as 1.71875, or almost one and three quarter days.

To get the hourly rate you need to multiply by 24 so
=E2*20*24

Thank you for that explanation Toadstool. I know in excel I can write the script to not show the zeros or empty spaces. I know conditional formatting would do it but I was wanting to put it in the formula like excel if possible.

 

[Fluff]

How about

=IFERROR(1/(1/(D4-C4)),"")

 

[jltjr75]

How about

=IFERROR(1/(1/(D4-C4)),"")

Fluff, that actually did the trick. Thank you.

 

[Fluff]

Glad we could help & thanks for the feedback.