如何使用vba将yyyymm数字字符串转换为月末日期? [英] how to convert yyyymm number string into end month date using vba?

问题描述

当前，在我的Excel中，有一串年份和月份写为yyyymm. 例如:201706

Currently, in my Excel, there is a string of year and month written as yyyymm. Eg: 201706

现在，我想在另一个工作表中将其转换为月末日期.例如:20170630

Right now I would like to convert it into the end of month date in another worksheet. Eg: 20170630

现在的问题是，如何告诉Excel根据字符串中的月份值自动创建日期?

The question now is, how do I tell Excel to auto create the day based on the month value in the string?

我打算使用if语句根据字符串后面的结束值声明每个月. (即:如果2017 06 ，日期= 20170630)

I was planning on using if statements to declare each month based on the ending value at the back of the string. (ie: If 201706 , date = 20170630)

但是后来我认为这不适用于2月的leap年.除此之外，还有其他方法可以使用吗?

But then I thought that won't work for February's leap year. Is there another method to use besides that?

推荐答案

尝试一下

dtmDate = 201706 (or put there value from cell)

dhLastDayInMonth = DateSerial(Left(dtmDate, 4), Right(dtmDate, 2) + 1, 0)

这篇关于如何使用vba将yyyymm数字字符串转换为月末日期?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！