0

Using Excel Mac 2011. I am trying to create an invoice based on a time sheet in a workbook. I want the invoice to show a worked date only if a date is entered on the time sheet. When I link the cells between the 2 worksheets with ='Time Card'!B6, I get the date 1/0/00 on the invoice when there is no date entered on the time sheet. I tried IFERROR, =IFERROR('Time Card'!B5,""), but get the same result. How can I have it generate a blank value instead of that? Thanks

1 Answer 1

0

You just need to update formatting of the cell using a custom format. This page has the basics.

key point is this sentence along with the table of date formats

It is very important to understand how cell formats are seen by Excel. Excel see a cells format as having four Sections. These are, from left to right Positive numbers, Negative Numbers, Zero Values and Text values. Each of these Sections are separated by a semi colon (;)

Basically you need to create a format that does something different for 0 values since this is what excel is returning.

yyyy-mmm-dd would return 2017-Apr-19 for today 1900-Jan-01 for 0 (day 0 in excels dating system) and an error for any negative values.

dd/mm/yyyy;"neg value";- would still return 2017-Apr-19 for today a "-" for 0 and the text "neg value" if you get a negative value for some reason

Drop the "-" at the end and it would return a blank for 0 values.

dd/mm/yyyy;"neg value";

4
  • Thanks. I think I understand. The value I am trying to format is actually the difference between 2 times. So I would use hh:mm ? Also to do this for a text field use "text"?
    – Jay P
    Apr 19, 2017 at 19:39
  • Not sure what you mean for a text field if you are comparing two times it should be a number. For formatting you could use [hh]:mm;; which would return a blank cell if it is equal to 0. The [] just allows the hour values to go higher then 24 which is likely not needed unless you are summing the values together.
    – gtwebb
    Apr 19, 2017 at 19:43
  • got it, thanks. I had another field that was coming as "0" when no value so I formatted it as "";neg value"; and that worked. Thanks for your help!
    – Jay P
    Apr 19, 2017 at 20:07
  • Should mention the "neg value" is just to be more explicit as to why the times are erroring its not really needed.
    – gtwebb
    Apr 19, 2017 at 20:17

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .