writing Now() to a cell but hiding date format ?

[JumboCactuar]

Hi,

when i write date/time to a cell like this:
Range("G2").Value = Now

it outputs like: 19/09/2018 01:32:24

which is correct but im wanting it to display time only. I need the date for various calculations so cant just use Time() function.
I can manually set format to hh:mm after writing to cell but wondering if it can be done with vba

so i need Now() to display 01:32 but still hold the date value (43362.0641666667) if that makes sense

thanks for any help

 

[Eric W]

Try:

Range("G2").NumberFormat = "hh:mm"

 

[JumboCactuar]

Try:

Range("G2").NumberFormat = "hh:mm"

your code works:
Range("G2").Value = Now
Range("G2").NumberFormat = "hh:mm"


but i get this when trying with worksheet_change event:

im writing the time to cell with:

Range("G" & Target.Row).Value = Now()
    Range("G" & Target.Row).NumberFormat = "hh:mm"

the first line works

 

[Eric W]

That exact code works for me. Do you have any kind of protection on that sheet?

 

[JumboCactuar]

That exact code works for me. Do you have any kind of protection on that sheet?

ye this is why thanks
the column is unprotected but the sheet protection restricted formatting so changed that