2 questions - one about dates, the other time

jennfurr

New Member
Joined
Apr 8, 2002
Messages
38
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.
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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.
 
Upvote 0
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.
 
Upvote 0
Hi,

For Q1

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

with the cell formatted as the time format you want.

Regards,
Jay
 
Upvote 0
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
 
Upvote 0
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")

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

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

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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top