VBA How to omit blank cells and avoid returning 00/01/1900?

Question

The AE, AG, AH, whenever the D or AD parallel cells are empty, return e.g. date of 00/01/1900 or time as 00:00. Can you please clarify how to return blank if the same parallel cell in D or AD is blank? Thanks

Sub valuedifference()

    Dim Total As Double
    Dim TimeX As Date
    Dim TimeY As Date
    Dim LastRow As Long
    Dim i As Long
    
    With ThisWorkbook.Sheets("Test1")
        LastRow = .Cells(.Rows.Count, "d").End(xlUp).Row
        For i = 2 To LastRow
            TimeX = CDate(.Range("d" & i).Value)
            TimeY = CDate(.Range("ad" & i).Value)
            Total = TimeValue(TimeY) - TimeValue(TimeX)
            .Range("ae" & i).Value = Total
            .Range("ag" & i).Value = Abs(Total * 24)
            .Range("ah" & i).Value = Abs(Total * 1440)
        Next i
    End With

End Sub

[If `IsEmpty` isn't working for you](https://stackoverflow.com/questions/67610486/vba-how-to-add-a-loop-with-application-worksheetfunction#comment119530348_67610775), then presumably those cells have a formula returning a `""`. Check if the `Len` of their `.Value` equals 0. — BigBen, May 20 '21 at 17:11
They do not have a formula. They are completely empty. Len=0 — Alien_Explorer, May 20 '21 at 17:13
If `IsEmpty` isn't working, then they *aren't* actually empty, or you're using it incorrectly. Perhaps a good idea to [edit] your question with how you're using `IsEmpty`. — BigBen, May 20 '21 at 17:14
They are empty. It must be an issue with the application of IsEmpty (on my side). — Alien_Explorer, May 20 '21 at 17:16
Which is why I suggested that you [edit] your question so one can debug what you're actually doing, instead of guessing. — BigBen, May 20 '21 at 17:16
If those cells are indeed empty, then you also check if their `.Value = 0`. — BigBen, May 20 '21 at 17:36

score 2 · Accepted Answer · edited May 24 '21 at 19:33

2

I think this a formatting issue- If TimeY is "" and TimeX is "", then Total = TimeValue(TimeY) - TimeValue(TimeX) is 0.

0 in time format is 0:0:00 and 0 in date format is 00/01/1900

One solution to this is to include an if statement that checks that there are not blank cells

For i = 2 To LastRow
    If .Range("D" & i).Value <> "" And .Range("AD" & i).Value <> "" Then
        TimeX = CDate(.Range("d" & i).Value)
        TimeY = CDate(.Range("ad" & i).Value)
        Total = DateDiff("n", TimeY, TimeX)
        .Range("AE" & i).Value = Total
        .Range("AG" & i).Value = Format(Abs(Total), "#.##")
        .Range("AH" & i).Value = Format(Abs(Total), "#.##")
    End If
Next i

edited May 24 '21 at 19:33

Alien_Explorer

859
1
9
22

answered May 20 '21 at 17:48

Maya

134
8

1

@BigBen can you provide an alternative please? – Alien_Explorer May 20 '21 at 17:51
2

`If Not IsEmpty(.Range("d" & i.Value) And Not IsEmpty(.Range("AD" & i).Value) Then`. Which was *already* proposed to OP. – BigBen May 20 '21 at 17:52
1

I edited my answer to use an if statement instead. Thanks @BigBen, GoTo is not advisable – Maya May 20 '21 at 17:55
Much appreciated @Maya . Thank you :) – Alien_Explorer May 20 '21 at 18:04
Interestingly, I have noticed that the code doesn't recognize the change in a day between two dates. So, e.g. if date is 23/05/21 12:00 and we subtract 24/05/21 13:00 from it it only shows the difference of 1 hour and not 25 hours same goes for minutes where it would be simply 60 mins and not 1500 minutes? Can you advise please? – Alien_Explorer May 23 '21 at 17:53
Got it - DateDiff("n", x, y) is the solution :) – Alien_Explorer May 23 '21 at 18:23

VBA How to omit blank cells and avoid returning 00/01/1900?

1 Answers1