Taking minutes from date time cell in VB

[rwrichard1]

Hi,

I have a cell with the following in it:

20/01/2018 02:03:00

I am using VB and calling for the Hour value like this...

UserFrom5.TextBox2 = WorksheetFunction.Text(Sheet6.Cells(a, 4), "hh")

This gives me 02. Perfect... exactly what I want.

How do I get the Minutes though?

UserFrom5.TextBox2 = WorksheetFunction.Text(Sheet6.Cells(a, 4), "hh:mm")

The above gives me 02:03... but

UserFrom5.TextBox2 = WorksheetFunction.Text(Sheet6.Cells(a, 4), "mm")

gives me 01... which I assume is the month instead.

I tried Nn, nn etc, but it doesn't work.

I tried...

UserFrom5.TextBox2 = Minute((Sheet6.Cells(a, 4))

But this gives me 3... and I want 03.

Please Help...?

Thanks



[TABLE="width: 111"]
<tbody>[TR]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

 

[ranman256]

m is month

n is minutes

 

[rwrichard1]

Thanks, but as I state in my post above, tried n, and it doesn't work....?

 

[wideboydixon]

Alternatives:

UserFrom5.TextBox2 = Format((Sheet6.Cells(a, 4).Value * 24) Mod 24, "00")
UserFrom5.TextBox2 = Format((Sheet6.Cells(a, 4).Value * 1440) Mod 60, "00")

WBD

 

[rwrichard1]

Thanks for having a look WBD. Unfortunately, the alternatves still do not give the minutes for 20/01/2018 01:00:00 Could it have something to do with the fact I have a double space after the year before the time? If I ask for hh:mm then it works absolutely fine, giving me the full time from the cell.. if I ask for hh.. it works fine giving me the hours... its just the minutes I cant seem to suss... really confusing me now.

 

[rwrichard1]

I've fixed it by doing this... No idea why I had to, but if it works...

UserForm5.TextBox2 = WorksheetFunction.Text(Sheet6.Cells(a, 4), "hh")
getseconds = WorksheetFunction.Text(Sheet6.Cells(a, 4), "hh:mm")
UserForm5.TextBox3 = Format(getseconds, "nn")

 

[wideboydixon]

I forgot about DatePart which might be useful in future:

UserForm5.TextBox2 = DatePart("h", Sheet6.Cells(a, 4).Value)
UserForm5.TextBox3 = DatePart("n", Sheet6.Cells(a, 4).Value)

WBD