Formulating AM to PM automatically

**SonjaRoark** · 07-25-2019, 09:14 AM

I am currently working on a work week schedule with beginners to the computer world. I have created a spreadsheet which contains the Time in, Lunch in, Lunch out and Time out. At the end of the column I have a total hours per day and at the bottom of the total column I have a total hours for the week.

Ok, now you can see what I am working on and now to my question. I know if I use the +0.5 it will automatically change. But what I am wondering is, is there a way to have a macro hidden into certain columns so when the people in my office type in 3:00 it will automatically go to 3:00 PM without them having to do anything else but put in their time?

**AliGW** · 07-25-2019, 09:18 AM

How will Excel know that they mean 3pm and not 3am?

**SonjaRoark** · 07-25-2019, 09:25 AM

I was thinking that I could have the Lunch In, Lunch Out and Time Out defaulted to a PM time and the Time In as the standard AM

**AliGW** · 07-25-2019, 09:27 AM

That could work as long as you are 100% sure that you will NEVER have a case where it wouldn't work. You need to be certain of this first, so think through all possible scenarios.

**SonjaRoark** · 07-25-2019, 09:40 AM

the only possible scenario I can see is if someone takes a lunch early and/or has to leave early, but for the most part of it would all be set. So if it is possible then if I do whatever I need to do, how if by chance would I fix or maybe have a one time fix in case of the onetime leaving early?

**AliGW** · 07-25-2019, 10:01 AM

If there is any possibility of it not working, you shouldn't do it.

Let's see a workbook with a few lines of dummy data covering all possibilities.

**SonjaRoark** · 07-25-2019, 10:16 AM

Time In Lunch In Lunch Out Time Out Hours Total
12:00 AM 12:00 AM 12:00 AM 12:00 AM 0.00
8:00 AM 1:00 PM 2:00 PM 5:00 PM 8.00
8:00 AM 11:15 AM 12:15 PM 5:00 PM 8.00
8:00 AM 12:00 PM 12:00 PM 9:30 AM 1.50
8:00 AM 1:00 PM 2:00 PM 5:00 PM 8.00

this would be the best way I could show you of all possible outcomes. but for the most part people will be working their regular schedule.

**AliGW** · 07-25-2019, 10:28 AM

Will you please attach a small sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

**FDibbins** · 07-25-2019, 10:40 AM

As far as excel is concerned, Time is actually a decimal part of 1 (day), so 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 6 PM) is 0.75

So if your users are entering 3.00 then that is not time, it is just the number 3 (as in 1, 2, 3, 4, 5 etc), wheres 3 (am) = 0.125 and 3 (pm) = 0.625

Apart from being able to ID 3 AM from 3 PM, you will also need to convert (the number) 3 to a real time - which could possibly mean using a helper column.

**SonjaRoark** · 07-25-2019, 10:41 AM

This is the sample I have been working with

**FDibbins** · 07-25-2019, 10:54 AM

I dont really see what the problem is here, it looks to me like your times where entered as TIME, along with the Am and PM designation?

1 small change I would make, would be to maintain TIME throughout ie - dont *24, just format as time 13:30 (under TIME options)

Also, no need to use SUM there, just =(D3-C3)+(F3-E3)

**SonjaRoark** · 07-25-2019, 11:10 AM

As I stated in the first place, I really don't want to use the army time as alot of the people here do not know/want to calculate army time, I want them to be able to just type in 3:00 or 3:15 or whatever and it automatically calculates it to being AM or PM so when I calculate the time it comes up with 8 hours or how many hours they worked for that day. I am specifically looking to see if there is a macro or something I can put in place so when they type 3:00 it will show up as 3:00 PM and not 3:00 AM

**AliGW** · 07-25-2019, 11:18 AM

Sonja - this doesn't quite add up. If anyone types 8 into a cell in column C, it does NOT return 8am, so how exactly are your users entering the data?

**SonjaRoark** · 07-25-2019, 11:30 AM

they will usually just type in 8:00 or 8:15.... The thing is in reality if the cells would just have 8:00, 1:00, 2:00, 5:00 and still be able to add up to a day in the total I can work with that, if not then I would need the 1, 2, 5 to all read PM in order for it to add up to a day in the total. but we all type in the hour : minutes

**JeteMc** · 07-29-2019, 10:33 PM

Looking at the sample file Tuesday's lunch in is an AM time, so you couldn't just say that all times in that column are PM.
What about relieving people of having to type in times and have them pick using a drop down that would only have the correct choices.
In the attached copy of the sample file, the list of times is in column Q and data validation is applied to C16:F22.
Note that the times for Saturday and Sunday are left blank.
Let us know if you have any questions.

Formulating AM to PM automatically

LinkBack

Thread Tools

Rate This Thread

Display

Formulating AM to PM automatically

Re: Formulating AM to PM automatically

Re: Formulating AM to PM automatically

Re: Formulating AM to PM automatically

Re: Formulating AM to PM automatically

Re: Formulating AM to PM automatically

Re: Formulating AM to PM automatically

Re: Formulating AM to PM automatically

Re: Formulating AM to PM automatically

Re: Formulating AM to PM automatically

Re: Formulating AM to PM automatically

Re: Formulating AM to PM automatically

Re: Formulating AM to PM automatically

Re: Formulating AM to PM automatically

Re: Formulating AM to PM automatically

Thread Information

Users Browsing this Thread

Similar Threads

Formulating a new date

Formulating Data

Formulating Data

need help formulating

Formulating Values

formulating Max Lookup(?)

Formulating between worksksheets

Bookmarks

Bookmarks

Posting Permissions