2 questions - one about dates, the other time

[jennfurr]

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.

[Anne Troy]

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.

[jennfurr]

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.

[Jay Petrulis]

Hi,

For Q1

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

with the cell formatted as the time format you want.

Regards,
Jay

[Aladin Akyurek]

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")

Aladin

[Jay Petrulis]

On 2002-04-09 17:25, Aladin Akyurek wrote:

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")

Aladin

Hi Aladin,

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

[Aladin Akyurek]

On 2002-04-09 17:29, Jay Petrulis wrote:

Hi Aladin,

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,

Aladin

[jennfurr]

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 ]

[Aladin Akyurek]

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.

Aladin