Auto Inserting AM or PM Reference Based On System Time Clock

[pctdoug]

I have an excel time tally sheet where users enter start and start times based on the "1:30 PM" non-24hr time format. With this approach, users must enter "_space_PM" for afternoon and evening entries, in order to prevent Excel from defaulting to creating an AM entry.

The problem is, users sometimes forget to enter the "_space_PM" part, which then messes up the downstream formula calculations.

Is there a way for us to continue entering times using the non-24hour format, but without having to manually type "_space_PM" for the afternoon and evening entries? I'm wondering if there is a way to have Excel determine whether it is AM or PM based on the system clock. We are working in a PC windows environment.

Thanks,
Doug

 

[gordsky]

I have an excel time tally sheet where users enter start and start times based on the "1:30 PM" non-24hr time format. With this approach, users must enter "_space_PM" for afternoon and evening entries, in order to prevent Excel from defaulting to creating an AM entry.

The problem is, users sometimes forget to enter the "_space_PM" part, which then messes up the downstream formula calculations.

Is there a way for us to continue entering times using the non-24hour format, but without having to manually type "_space_PM" for the afternoon and evening entries? I'm wondering if there is a way to have Excel determine whether it is AM or PM based on the system clock. We are working in a PC windows environment.

Thanks,
Doug

this could be done ONLY if the user is putting in the time at the time they actually start work and not earlier or retrospectively. Is this what they are doing?

 

[pctdoug]

this could be done ONLY if the user is putting in the time at the time they actually start work and not earlier or retrospectively. Is this what they are doing?

Thanks for responding gordsky... and it is a "yes" to your question. Users enter their times in real time.

 

[gordsky]

so the easiest way to do it would be to use a custom format in the relevant column. If you select custom and then chose (if available) or enter
h:mm AM/PM then if a user forgets it will add it and if they include it it wont do anything

 

[pctdoug]

so the easiest way to do it would be to use a custom format in the relevant column. If you select custom and then chose (if available) or enter
h:mm AM/PM then if a user forgets it will add it and if they include it it wont do anything

Nice... that is a simple fix. It is still morning in my PST time zone so I can't test quite yet... but looking forward to having a few less keystrokes from this point on. Thank you!

 

[gordsky]

N

Nice... that is a simple fix. It is still morning in my PST time zone so I can't test quite yet... but looking forward to having a few less keystrokes from this point on. Thank you!

not a problem, shouldnt be any issue with AM/PM as it uses the system clock hence why I asked if it entered in real time. If this solves your query then please mark as resolved and click the tick next to the answer

 

[pctdoug]

N

not a problem, shouldnt be any issue with AM/PM as it uses the system clock hence why I asked if it entered in real time. If this solves your query then please mark as resolved and click the tick next to the answer

Problem solved! It is currently afternoon in my time zone, and your h:mm AM/PM format suggestion works like you predicted. I will mark this thread as resolved. Thanks again for your help.

 

[gordsky]

glad I could hel

Problem solved! It is currently afternoon in my time zone, and your h:mm AM/PM format suggestion works like you predicted. I will mark this thread as resolved. Thanks again for your help.

glad I could help and thanks for the feedback

 

[pctdoug]

Oops... I guess I spoke too soon. Although the solution worked earlier, it is no longer working and all the times entered now default to AM again. Any ideas why it worked and then stopped working? Very strange.

 

[pctdoug]

Oops... I guess I spoke too soon. Although the solution worked earlier, it is no longer working and all the times entered now default to AM again. Any ideas why it worked and then stopped working? Very strange.

@gordsky or anyone - after thinking this through a bit more, I can see that it is not that the solution stopped working... it is just that in order for the solution to work, any afternoon numbers must be added using the 24hr formatting system which then auto converts to the AM/PM system. So, if I want "1:30 PM" to show in the cell, I have to enter "13:30"

What I was actually hoping for, is to simply enter "1:30" in the cell and have it auto convert to "1:30 PM"

Do you know if there is a formatting option (or a formula) that will do that?