Needing Foratting formula to set time to 24 hours

[JJFletcher]

Hello All,

Needing to update this code so that the formula format produces 24 hr time

This is the information that this string produces = 44542 (This is in the cell) when I need it to produce = 14:15:00

Please Login or Register  to view this content.

Any thoughts would be helpful

Regards,

John

[janmorris]

are you looking for a standard excel formula, or a formula to be used in VBA or something else?

the reason i ask is that the code you have provided is not a standard excel formula, and therefore the enquiry doesnt make sense in its current format.

[JJFletcher]

Hi Janmorris,

So this would fall under a Macro?

[MrShorty]

The formula appears to be rounding a date+time stamp to the nearest 15 minutes. A result of 44542 is the date/time serial number for midnight on the morning of 21 Dec. 2021.

If your intention is to simply use Excel's date/time serial number system, then it sounds like you need to add a suitable number format for this cell (Range.NumberFormat property: https://docs.microsoft.com/en-us/off...e.NumberFormat See this help page for valid number format codes: https://support.microsoft.com/en-us/...7-9c9354dd99f5 ).

A number format of "yyyy/mm/dd hh:mm:ss" will display a date/time serial number with the full date time information (24 hour clock). The number 44542 will display as 2021/12/21 0:00:00.

Is that what you are trying to do, or am I misunderstanding something?

[JJFletcher]

Mr Shorty,

You are accurate... I am attempting to once the formula pulls the information from the cell in Column A is then to convert that to a time stamp to the nearest 15 minutes.

[JJFletcher]

I need to format to 24 hour clock ... The user can select to format and select time and then 13:30:55, but I want VBA to do the conversion.

I have tried

Please Login or Register  to view this content.

But I am getting #VALUE! error

Any thoughts would be appreciated!

Regards,

John

[JJFletcher]

UPDATE:

I have changed the code to reflect this and the #VALUE! changes and provides the time - yet I need the time to show in 15 minutes intervals

Please Login or Register  to view this content.

[PCI]

Perhaps next formula
=TEXT(INT(A1*24*60/15)*15/60/24;"hh:mm:ss")

[JJFletcher]

Hi PCI,

I really appreciate your feedback Thanks!!!

When attempting this solution I receive a Syntax Error - Compile Error


I tried by adding "" to the formula and it changes the error from Syntax Error - Compile Error to Application-Defined or Object-defined error

changed to

Please Login or Register  to view this content.

[PCI]

See next statement

Please Login or Register  to view this content.

[JJFletcher]

PCI,

That did the job - It works - Thank you so much

Have a great New Year

[PCI]

Good news.
Have a great new year to , with Excel of course and thank you for the rep.

[MrShorty]

You've marked this as solved, so there may be nothing more to really add. However, I would point out that the solution you've settled on (using the TEXT() function) stores a time (a number) as text. We have frequent threads on this forum about problems with numbers stored as text. It is up to you as the programmer to make this decision. Before settling on this solution, I would suggest that you consider what problem you are solving by storing the time/number as text and contrast that against other potential problems that you are creating by storing the time/number as text. Make sure that you like having a time/number stored as text.