VBA excel - cells containing dates return empty values

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}

-1

for some reason I am unable to run functions such as Datediff (e.g. Range("K2") = DateDiff("yyyy", A2, J2) as VBA show "A2" and "J"2 as empty - but they aren't (and they are formatted as dates i.e. "yyyy/mm/dd".

Has anyone ever had to deal with something like this before?

edited Jan 3 at 12:43

Cindy Meister

16.1k102537

asked Jan 3 at 11:56

Lev

You need to also specify A2 and J2 as a Range objects. With the code you show VBA will interpret A2 and J2 as a variable names. So: Range("K2") = DateDiff ("yyyy", Range("A2").Value, Range("J2").Value)` (Note: you should also add Option Explicit at the top of the code module, then this kind of thing won't happen to you.)

– Cindy Meister
Jan 3 at 12:45

Yes! it works! Thank you very much Cindy really appreciated! Lev

– Lev
Jan 3 at 13:23

add a comment |

-1

Has anyone ever had to deal with something like this before?

edited Jan 3 at 12:43

Cindy Meister

16.1k102537

asked Jan 3 at 11:56

Lev

You need to also specify A2 and J2 as a Range objects. With the code you show VBA will interpret A2 and J2 as a variable names. So: Range("K2") = DateDiff ("yyyy", Range("A2").Value, Range("J2").Value)` (Note: you should also add Option Explicit at the top of the code module, then this kind of thing won't happen to you.)

– Cindy Meister
Jan 3 at 12:45

Yes! it works! Thank you very much Cindy really appreciated! Lev

– Lev
Jan 3 at 13:23

add a comment |

-1

Has anyone ever had to deal with something like this before?

edited Jan 3 at 12:43

Cindy Meister

16.1k102537

asked Jan 3 at 11:56

Lev

Has anyone ever had to deal with something like this before?

excel vba

edited Jan 3 at 12:43

Cindy Meister

16.1k102537

asked Jan 3 at 11:56

Lev

edited Jan 3 at 12:43

Cindy Meister

16.1k102537

asked Jan 3 at 11:56

Lev

edited Jan 3 at 12:43

Cindy Meister

16.1k102537

edited Jan 3 at 12:43

Cindy Meister

16.1k102537

edited Jan 3 at 12:43

Cindy Meister

16.1k102537

asked Jan 3 at 11:56

Lev

asked Jan 3 at 11:56

Lev

asked Jan 3 at 11:56

Lev

You need to also specify A2 and J2 as a Range objects. With the code you show VBA will interpret A2 and J2 as a variable names. So: Range("K2") = DateDiff ("yyyy", Range("A2").Value, Range("J2").Value)` (Note: you should also add Option Explicit at the top of the code module, then this kind of thing won't happen to you.)

– Cindy Meister
Jan 3 at 12:45

Yes! it works! Thank you very much Cindy really appreciated! Lev

– Lev
Jan 3 at 13:23

add a comment |

You need to also specify A2 and J2 as a Range objects. With the code you show VBA will interpret A2 and J2 as a variable names. So: Range("K2") = DateDiff ("yyyy", Range("A2").Value, Range("J2").Value)` (Note: you should also add Option Explicit at the top of the code module, then this kind of thing won't happen to you.)

– Cindy Meister
Jan 3 at 12:45

Yes! it works! Thank you very much Cindy really appreciated! Lev

– Lev
Jan 3 at 13:23

You need to also specify A2 and J2 as a Range objects. With the code you show VBA will interpret A2 and J2 as a variable names. So: Range("K2") = DateDiff ("yyyy", Range("A2").Value, Range("J2").Value)` (Note: you should also add Option Explicit at the top of the code module, then this kind of thing won't happen to you.)

– Cindy Meister
Jan 3 at 12:45

Yes! it works! Thank you very much Cindy really appreciated! Lev

– Lev
Jan 3 at 13:23

add a comment |

2 Answers
2

active

oldest

votes

you mean

    Range("K2") = DateDiff("yyyy", Range("A2"), Range("J2"))

in other way
Vba show them as empty, coz they are empty as Variables

answered Jan 3 at 12:46

Jimmy M.

763

Jimmy - got it thank you very much! Silly mistake on my behalf. Lev

– Lev
Jan 3 at 13:24

add a comment |

You need to name the cells as objects in the function. That would Range(“A2”) and Range(“J2”).

It is also important that you tell the función you need the value of that object, what’s really in the cell, so add .value at the end of each cell range (e.g. Range(“A2”).value)

Also, that funciona will return something like this “yyyy0103” and not “yyyy/01/03” as you expect.

Add this:
DateDiff(“yyyy/“, Range(“A2”).value & “/“, Range(“J2”).value)

answered Jan 3 at 13:14

Pablo G

736

Thank you very much! That was it! all works now. Thank you Pablo.

– Lev
Jan 3 at 13:24

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54021813%2fvba-excel-cells-containing-dates-return-empty-values%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

you mean

    Range("K2") = DateDiff("yyyy", Range("A2"), Range("J2"))

in other way
Vba show them as empty, coz they are empty as Variables

answered Jan 3 at 12:46

Jimmy M.

763

Jimmy - got it thank you very much! Silly mistake on my behalf. Lev

– Lev
Jan 3 at 13:24

add a comment |

you mean

    Range("K2") = DateDiff("yyyy", Range("A2"), Range("J2"))

in other way
Vba show them as empty, coz they are empty as Variables

answered Jan 3 at 12:46

Jimmy M.

763

Jimmy - got it thank you very much! Silly mistake on my behalf. Lev

– Lev
Jan 3 at 13:24

add a comment |

you mean

    Range("K2") = DateDiff("yyyy", Range("A2"), Range("J2"))

in other way
Vba show them as empty, coz they are empty as Variables

answered Jan 3 at 12:46

Jimmy M.

763

you mean

    Range("K2") = DateDiff("yyyy", Range("A2"), Range("J2"))

in other way
Vba show them as empty, coz they are empty as Variables

answered Jan 3 at 12:46

Jimmy M.

763

answered Jan 3 at 12:46

Jimmy M.

763

answered Jan 3 at 12:46

Jimmy M.

763

answered Jan 3 at 12:46

Jimmy M.

763

Jimmy - got it thank you very much! Silly mistake on my behalf. Lev

– Lev
Jan 3 at 13:24

add a comment |

Jimmy - got it thank you very much! Silly mistake on my behalf. Lev

– Lev
Jan 3 at 13:24

Jimmy - got it thank you very much! Silly mistake on my behalf. Lev

– Lev
Jan 3 at 13:24

add a comment |

You need to name the cells as objects in the function. That would Range(“A2”) and Range(“J2”).

It is also important that you tell the función you need the value of that object, what’s really in the cell, so add .value at the end of each cell range (e.g. Range(“A2”).value)

Also, that funciona will return something like this “yyyy0103” and not “yyyy/01/03” as you expect.

Add this:
DateDiff(“yyyy/“, Range(“A2”).value & “/“, Range(“J2”).value)

answered Jan 3 at 13:14

Pablo G

736

Thank you very much! That was it! all works now. Thank you Pablo.

– Lev
Jan 3 at 13:24

add a comment |

You need to name the cells as objects in the function. That would Range(“A2”) and Range(“J2”).

It is also important that you tell the función you need the value of that object, what’s really in the cell, so add .value at the end of each cell range (e.g. Range(“A2”).value)

Also, that funciona will return something like this “yyyy0103” and not “yyyy/01/03” as you expect.

Add this:
DateDiff(“yyyy/“, Range(“A2”).value & “/“, Range(“J2”).value)

answered Jan 3 at 13:14

Pablo G

736

Thank you very much! That was it! all works now. Thank you Pablo.

– Lev
Jan 3 at 13:24

add a comment |

You need to name the cells as objects in the function. That would Range(“A2”) and Range(“J2”).

It is also important that you tell the función you need the value of that object, what’s really in the cell, so add .value at the end of each cell range (e.g. Range(“A2”).value)

Also, that funciona will return something like this “yyyy0103” and not “yyyy/01/03” as you expect.

Add this:
DateDiff(“yyyy/“, Range(“A2”).value & “/“, Range(“J2”).value)

answered Jan 3 at 13:14

Pablo G

736

You need to name the cells as objects in the function. That would Range(“A2”) and Range(“J2”).

It is also important that you tell the función you need the value of that object, what’s really in the cell, so add .value at the end of each cell range (e.g. Range(“A2”).value)

Also, that funciona will return something like this “yyyy0103” and not “yyyy/01/03” as you expect.

Add this:
DateDiff(“yyyy/“, Range(“A2”).value & “/“, Range(“J2”).value)

answered Jan 3 at 13:14

Pablo G

736

answered Jan 3 at 13:14

Pablo G

736

answered Jan 3 at 13:14

Pablo G

736

answered Jan 3 at 13:14

Pablo G

736

Thank you very much! That was it! all works now. Thank you Pablo.

– Lev
Jan 3 at 13:24

add a comment |

Thank you very much! That was it! all works now. Thank you Pablo.

– Lev
Jan 3 at 13:24

Thank you very much! That was it! all works now. Thank you Pablo.

– Lev
Jan 3 at 13:24

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

Search This Blog

Ufyukyu