Convert to military time - part II

[Carole O]

I am trying to convert MM/DD/YYYY H:mm:ss AM/PM to military time. I copied
the macro provided by Bernie Deitrick to Soccer Star, and got the same error
message with ..Value = TimeValue(TimeStr). I've named the start time
TimeStr, but don't know what to do next.

TIA,

Carole O

 

[sebastienm]

Hi,
You can just format the cells containing the military times:
-select the cells (or entire column)
-menu Format > Cells, tab Number:
-choose Time as category
- choose a non-AM/PM type, eg: 13:30:55

Regards,
Sébatien

 

[sebastienm]

Please, disregard my previous post.

-What is your input:
you have a date data type variable or a date in a string ? if string, in
which format?
-What do you want as output:
a string (containing a date) formatted as MM/DD/YYYY H:mm:ss AM/PM ?

I suppose you can use the Format function. Example:
Dim d as date
d=Now()
msgbox "US: " & format(d, "mm/dd/yyy h:mm:ss AM/PM")
msgbox "Military: " & format(d, "mm/dd/yyy h:mm:ss")

Regards,
Sebastien

 

[Tom Ogilvy]

to elaborate. There is no military time or AM/PM time. There is only time.
Time is stored as fraction of a 24 hour day, so if a .5 is stored, it is 1/2
a day or 12:00 PM. You can then format the cell to display in any supported
manner.

any whole numbers in the value indicate the number of days from a base date.
The default is midnight just before 1/1/1900 so 1.5 would be 1/1/1900
12:00:00 AM/PM

38221.25 in a cell formatted as a date/time would be Aug 12, 2004 8:00:00
AM. you can format it to display anyway you want, including military time.

 

[Tom Ogilvy]

The key issue would be if the pasted data is stored as a date or a string.

It is unclear how subtracting provides what most people would describe as a
total.

 

[Carole O]

I want to use the formula from Chip Pearson's Working Days and Hours Between
Two Dates and Times. I can get it to work if I change the time to 'military'
time, but not if it is in the AM/PM time that crosses over from 12 to 1
AM/PM. All the time data on my spreadsheet has the AM/PM. I am looking for
a way to convert the AM/PM to 'military' time for all the columns in this
format.

How could I tell if the data is a data or a string?

I appreciate your help!!
Carole O

 

[Tom Ogilvy]

One more time, if a value in a cell is stored as a time value, it is neither
AM/PM or military. That is a way to display the time. I would suspect your
cells hold strings that look like displayed time (which won't work with
formulas which expect them to be dates/time) and when you do whatever it is
you do to convert them, you make them into actual time values (at which time
they work).

You can use a formula like

=IsText(A1) in B1 to check if A1 contains a string/text.

You can select the column with your data and do Edit=>Goto=>special and
select Constants and Text. If you date cells are then selected, they hold
text, not time values.