Time sheet formula (1 Viewer)

mahen

Registered User.
Local time
Today, 20:54
Joined
Jan 13, 2008
Messages
56
I have a problem if anyone could sort out please. When the over time come to 0 hours (column I) then I am getting ###### overtime column. How do I resolve?
file attached
Thanks
Mahen
 

Attachments

  • TimeSheet.xls
    52.5 KB · Views: 248

CJ_London

Super Moderator
Staff member
Local time
Today, 20:54
Joined
Feb 19, 2013
Messages
16,553
I suspect you are generating a negative value

Opened the file and hovered over the cell and it says 'Dates and times are negative or too large to display

Changed the end time to 23:31 and figure displays - so take the message to mean zero as well.

The problem is with your custom format - change it to

hh:mm;

and the cell will be blank for zero values
 

mahen

Registered User.
Local time
Today, 20:54
Joined
Jan 13, 2008
Messages
56
Thank you. Excellent, worked perfectly. I didn't know hh:mm; exist. I only know about hh:mm

Great,
Mahen
 

CJ_London

Super Moderator
Staff member
Local time
Today, 20:54
Joined
Feb 19, 2013
Messages
16,553
google 'vba format' to find out more - but depending on the datatype there are up 4 format conditions separated with a semi colon.

positive;negative;zero;null

numbers have 4 conditions, dates have 3 (no negative), text has 2 (no negative or zero)

One I have used on form controls would be for example

0.00;[red](0.00);"-","Please enter a number"

so depending on the control value they would appear as
so a positive number -> 12.22
a negative number -> (12.22)
a zero -> -
a null -> Please enter a number

Because these are format properties they do not affect the underlying data and you can appear to have text in a number control
 
  • Like
Reactions: Rx_

Users who are viewing this thread

Top Bottom