Thanks:  0
Likes:  0

1. 1. I have a four digit number that needs to be changed into one cell, as hh:mm. I know it has something to do with the HOUR and MINUTE command but I don't know what.

2. I have a date that I need to show the day of week. I also know that the end of the command is "ddd", but I don't know the rest of it.

I did both of these a looooong time ago.

2. First one, I'm not sure about. Do you have, for instance 0213 and 2213 or what? Can you give some examples? How do you tell if it is am or pm?

Second, just format the Cells, use Custom format, and type dddd in the bar at the right.

3. For the numbers: I have all types of numbers, all in military time, everything from 0001 up to 2359.

And thank you about the cell formatting. That seems to work.

4. Hi,

For Q1

=TIMEVALUE(LEFT(E4,LEN(E4)-2) & ":" & RIGHT(E4,2))

with the cell formatted as the time format you want.

Regards,
Jay

5. On 2002-04-09 17:18, jennfurr wrote:
For the numbers: I have all types of numbers, all in military time, everything from 0001 up to 2359.

And thank you about the cell formatting. That seems to work.
In B2 enter:

=(LEFT(A2,2)&":"&RIGHT(A2,2))+0

where A2 houses a value like 2359 (military time).

Format B2 as time.

BTW, if you have a date say in E1, you want to see the day in say F1, you can use:

=TEXT(E1,"ddd")

6. On 2002-04-09 17:25, Aladin Akyurek wrote:
On 2002-04-09 17:18, jennfurr wrote:
For the numbers: I have all types of numbers, all in military time, everything from 0001 up to 2359.

And thank you about the cell formatting. That seems to work.
In B2 enter:

=(LEFT(A2,2)&":"&RIGHT(A2,2))+0

where A2 houses a value like 2359 (military time).

Format B2 as time.

BTW, if you have a date say in E1, you want to see the day in say F1, you can use:

=TEXT(E1,"ddd")

Good one. Accomplishes the same thing as mine did, only shorter.

Unclear how times before 10:00am are listed. If the worksheet holds the leading zero, all is fine. If not, LEFT(A2,2) has to be changed to LEFT(A2,LEN(A2)-2)

Regards,
Jay

7. On 2002-04-09 17:29, Jay Petrulis wrote:
On 2002-04-09 17:25, Aladin Akyurek wrote:
On 2002-04-09 17:18, jennfurr wrote:
For the numbers: I have all types of numbers, all in military time, everything from 0001 up to 2359.

And thank you about the cell formatting. That seems to work.
In B2 enter:

=(LEFT(A2,2)&":"&RIGHT(A2,2))+0

where A2 houses a value like 2359 (military time).

Format B2 as time.

BTW, if you have a date say in E1, you want to see the day in say F1, you can use:

=TEXT(E1,"ddd")

Good one. Accomplishes the same thing as mine did, only shorter.

Unclear how times before 10:00am are listed. If the worksheet holds the leading zero, all is fine. If not, LEFT(A2,2) has to be changed to LEFT(A2,LEN(A2)-2)

Regards,
Jay
Jay,

The notational logic of the miltary time is such that:

10:00 am --> 1000
10:00 pm --> 2200

I assumed that the military time entries obey the military notational logic.

Regards,

8. and yes, since I work in a police facility, I have all my time-related cells formatted to show four digits including the leading 0.

Now the next question if anyone's still reading. How would I translate either the above time formula to show decimal time, or just make another formula to show the time in decimal format?

[ This Message was edited by: jennfurr on 2002-04-10 07:37 ]

9. On 2002-04-10 07:32, jennfurr wrote:
and yes, since I work in a police facility, I have all my time-related cells formatted to show four digits including the leading 0.

Now the next question if anyone's still reading. How would I translate either the above time formula to show decimal time, or just make another formula to show the time in decimal format?

[ This Message was edited by: jennfurr on 2002-04-10 07:37 ]
=(LEFT(A2,2)&":"&RIGHT(A2,2))*24

Format the cell of the formula as General.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•