Formatting Date Cell with No Values

[Curtis]

I’m using the TransferSpreadsheet Action in my access macro db to
automatically transfer data to an Excel spreadsheet. The data gets
transferred to the worksheet “qryRequirement _Status_NotComp_b†(Sheet 1).
In my adjacent worksheet “Not Completed Requirement†(Sheet 2), have
formulated cells to copy data from Sheet 1. One column heading is “Date
Completedâ€. The following formula is in the date cell of Sheet 2:
=+qryRequirement_Status_NotComp_b!F2; formatted as date fields. Excel 2003

In Sheet 1, not all the cells under the DateCompleted contain values. When
the data gets copied to Sheet 2, those date values that are empty in Sheet 1
result in a 0 (zero) in Sheet 2; resulting in 1/0/00. I want those cells
with no values to remain empty in Sheet 2. I haven’t used any coding in
Excel, and was wondering if there is a way to get this accomplished. Any
suggestions would be helpful.

 

[andy62]

One way would be to change your reference formula to include an IF statement
to trap for the blank cells on the first sheet. For instance:

=IF(qryRequirement_Status_NotComp_b!F2="","",qryRequirement_Status_NotComp_b!F2)

(I'm not sure why you had a "+" sin in front of your reference, assumed it
was an error).

Hope that helps.

 

[Curtis]

Yes, your post did exactly what I wanted. I copied this formula into the
Date Completed cell of Sheet 2 and only blank cells remained for those blank
cells in Sheet 1. Thanks for your professional reponse! (the + symbol was
there to "copy" the contents of the same cell in Sheet 2)....