Looking to avoid typing am/pm

[Benfried]

Hi, I'm hoping someone can help me with a formula as I'm not too advanced in excel but I'd imagine there is a way. We have a spreadsheet where we load in the start time and end time of assembly items and have a formula to then generate in the next column the total time it took to assemble the cabinet, like =b2-a2. I would like to enter start and end times where excel knows to recognize these that for example any hour starting with 7 until 11 is registered/displayed as am and any hour noted from 12-5 automatically is understood to mean pm - being that we don't have night shift as of yet so there never be a situation where 715 may mean 7pm. I hope I explained myself good enough. I am trying to avoid the tedious step of entering am / pm in each cell as we need to load lots a day...
Thanks in advance.

[Tony Valko]

See this...

http://www.cpearson.com/Excel/DateTimeEntry.htm

[Benfried]

Thanks Tony. Your link though does not help customize specific hours of the day to mean am while others to mean pm as I had mentioned in my question. I know it's not a simple formatting but I'm hoping it can be done...
Thanks
Ben

[Richard Buttrey]

Hi,

How are you entering the times?

If you enter say 8:00 rather than 8 then the cell can be formatted to display AM/PM automatically.

[Tony Valko]

If you were to enter the time in 24hr format:

A2 = Start = 715 (7:15 AM)

B2 = End = 1315 (1:15 PM)

=MOD(TEXT(B2,"00\:00")-TEXT(A2,"00\:00"),1)

Format as h:mm result = 6:00

[Benfried]

I enter now 710 but it displays as 7:10, currently using this format in the cell "0\:00" but frankly because this is a custom format (not time or text) I'm therefore currently not even getting column c to generate the total time it took to make the cabinet which is something I'd really want as well - so I can use some help in streamlining this assembly time sheet spreadsheet. old way was text but needed to enter the whole deal i.e. "9:25 am" etc. each time, and then there was this formula "=TEXT(F23939-E23939,"h:mm")" that generated the actual time it took to build that cabinet, and this seemed to work but due to lots of data entry I'm hoping to get a formula to avoid entering more than necessary...
I appreciate your help!

[Rob Kennedy]

Hello, I know a way to do it.

You can enter times like these samples (brackets for description only):

800 (e.g. means 8am)
830
1143
1202
1
130 (1:30pm)
155
200
239
5 (means 5pm)
515
559
6 (means 6pm)
630

into cell A1, and in your result cell, the answer is a true XL 'time', and is achieved using this formula:

Please Login or Register  to view this content.

Then play around with the formatting to show AM or PM (or 24 hour) as desired. If you want a difference between two times, use this formula to 'correct' the lazy text in start and finish time cells, and a simple subtraction sum between the two formula cells will give you the lapsed time.

I have used >659 twice in the formula, if you need your AM to start earlier, and your PM to finish earlier, you can adjust this cut-off to accommodate.

Give me a 'rep' if you like what I say! Thanks

[Tony Valko]

OK, did you try the suggestion in post 5?

[Benfried]

Tony - your way would requires using army time - someone here had suggested that fix... I guess I'd rather not have to start thinking and need to mentally constantly translate like that if I have an alternative.
Thanks!

[Tony Valko]

How do you enter a time like 5:00 PM?

5 or 500? Or something else?

[Benfried]

I now enter 500

[Richard Buttrey]

Well if you enter 500 can't you use the standard time format that shows AM/PM, in Excel's list of time formats.

[PHS8100]

Rob,
Would be so kind as to explain, the math behind this? I can't figure out the *100, also the 659.

[hoyasaxa215]

If you just upload a sample of the file, it might be easier to make the changes directly to the file and then upload it.

[Tony Valko]

I now enter 500

Try this...

Data Range

	A	B	C
1	Start	End	Total
2	715	1115	4:00
3	1200	100	1:00
4	700	100	6:00
5	930	1230	3:00
6	1200	1205	0:05
7	330	430	1:00
8	1100	215	3:15
9	430	500	0:30
10	700	230	7:30

This formula entered in C2:

=TEXT(B2,"00\:00")+IF(B2<A2,0.5,0)-TEXT(A2,"00\:00")

Format as h:mm

Copy down as needed.

[Tony Valko]

P.S.

I'm assuming the total time span is 7:00 AM to 5:00 PM.

[Benfried]

Thanks so much Rob and Tony - both work well. I think I will use Tony's as it's a bit less complicated formula and also doesn't require an extra column for the display. If I really needed the am/pm (as I myself thought in my question) then clearly Rob's formula offers that but in truth after thinking about it and seeing Tony's response I realize I don't need the am/pm to be displayed, rather simply an accurate total-time to build the cabinet generated - even if times involved is say 1225-105...
Thanks all!
Ben

[Tony Valko]

You're welcome. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

[Rob Kennedy]

Rob,
Would be so kind as to explain, the math behind this? I can't figure out the *100, also the 659.

=VALUE(
LEFT(TEXT(IF(VALUE(TEXT(IF(A1<13,A1*100,A1),"0000"))>659,0,1200)
+VALUE(TEXT(IF(A1<13,A1*100,A1),"0000")),"0000"),2)
&":"
&RIGHT(TEXT(IF(VALUE(TEXT(IF(A1<13,A1*100,A1),"0000"))>659,0,1200)
+VALUE(TEXT(IF(A1<13,A1*100,A1),"0000")),"0000"),2))

The use of 'A1*100' converts times entered as just the hour (with no minutes) like '9' OR '11' into a number that can then become a text string with two zeroes after the hour (i.e. it inserts the missing zero minutes).

The number '659' is the highest number that is regarded as PM, after that (700 and upwards) it assumes the time entered refers to AM. By use of the the logical condition >659. If you wanted to start later and finish later you could adjust this to say '>829', which would mean that 830 (08:30) would be calculated as AM, and 829 (8:29) would be regarded as PM.

Does that help?