Convert to military time - part II
 

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

Convert to military time - part II
 

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

"Carole O" wrote:

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

Convert to military time - part II
 

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

"Carole O" wrote:

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

Convert to military time - part II
 

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.

--
Regards,
Tom Ogilvy



"sebastienm" wrote in message
...
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

"Carole O" wrote:

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

Convert to military time - part II
 

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.

--
Regards,
Tom Ogilvy

"Carole O" wrote in message
...
The data is coming from a Crystal Report format (copy and paste into Excel
2003). I want to be able to subtract MM/DD/YYYY H:mm:ss AM/PM formatted
cells to get total work hours. I think I would have to convert the time
part
of the cell to military time in order to do the subtraction. I will try
your
suggestion. Thanks for your help!

Carole O

"sebastienm" wrote:

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

"Carole O" wrote:

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

Convert to military time - part II
 

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" wrote:

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.

--
Regards,
Tom Ogilvy

"Carole O" wrote in message
...
The data is coming from a Crystal Report format (copy and paste into Excel
2003). I want to be able to subtract MM/DD/YYYY H:mm:ss AM/PM formatted
cells to get total work hours. I think I would have to convert the time
part
of the cell to military time in order to do the subtraction. I will try
your
suggestion. Thanks for your help!

Carole O

"sebastienm" wrote:

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

"Carole O" wrote:

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

Convert to military time - part II
 

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.

--
regards,
Tom Ogilvy

"Carole O" wrote in message
...
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" wrote:

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.

--
Regards,
Tom Ogilvy

"Carole O" wrote in message
...
The data is coming from a Crystal Report format (copy and paste into
Excel
2003). I want to be able to subtract MM/DD/YYYY H:mm:ss AM/PM
formatted
cells to get total work hours. I think I would have to convert the
time
part
of the cell to military time in order to do the subtraction. I will
try
your
suggestion. Thanks for your help!

Carole O

"sebastienm" wrote:

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

"Carole O" wrote:

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