format cell to show a dash '-' if no date has been entered

[Anthony Pinnegar]

Can anyone point me in the right direction on how to format a cell so that it shows a dash (-) until a date is entered and then it will have the date as dd/mm/yy.

All the standard date formats automatically populate the cell with 00/00/01 instead of a dash, untilthe date is entered and then it becomes dd/mm/yy. Nowhere can I find the logic that initially applies a dash insteard of 00/00/01.

I have tried typing my own formatting instructions within, 'right click on mouse'>select format cells>select custom, but these don't work.

Any thoughts would be greatly received.

Thanks,

Anthony

 

[Pavan.Sada.PS]

Hello Anthony,

Not sure whether the attached file fulfills your requirement

 

[Anthony Pinnegar]

My start date (M6) is then linked to an Audit due date (O6) that is 45 days on from the start date that is entered, on the condition that R6 does not say 'Not applicable'.
Using the formula above Pavan, the Audit due date now says #VALUE. How do I then format O6 so that it shows a '-' until R6 either says not applicable/or or a date is showing in M6.

Thanks,

Anthony

 

[Somendra Misra]

Hi Anthony,

Custom format apply format on data available in cell not on blank cell, the thing that you are requiring may require VBA. Can you tell do you want this on particular column or row or on the entire sheet?

Regards,

 

[Pavan.Sada.PS]

Will this fulfill?...

 

[Khalid NGO]

Hi Anthony,
In addition to SM Sir,

Can we use the following custom format? e.g:
dd-mmm-yyyy;;"(-)";@

So it will show (-) when there is no date; when you insert date / number you will see the date format.
Please note you have to enter 0 (zero) for the blank dates; cell will show blank if it is empty.

Regards,

 

[Anthony Pinnegar]

Hi Anthony,

Custom format apply format on data available in cell not on blank cell, the thing that you are requiring may require VBA. Can you tell do you want this on particular column or row or on the entire sheet?

Regards,

I'd be looking for this in a particular column, SM.

I don't know anything about VBA tbh.

 

[Anthony Pinnegar]

In my spreadsheet attached I have added
dd/mm/yy::"-";@
as the cell format, and it is replicating #VALUE!, which it is reading from column M. How do I get column M to read '-' so that I can get the same thing in column H when the lines are blank, without affecting the cell calculations ?

 

[KenU]

Anthony,

See if the following will solve your issue. In cell H6 use the formula:

=IF(L6="In List", "Not applicable", IFERROR(F6+45,"-"))

I'm not sure of the overall intent of what you are trying to accomplish, but the dash would require a change in the formula in column M to test for "-" rather than "" to represent a blank in column H.

I hope that at least helps get you going in the right direction.

Regards,
Ken

 

[NARAYANK991]

Hi Anthony ,

I do not know what your exact requirement is , but I have rewritten some of the formulae in the uploaded file ; see if it helps.

Narayan