need a formula for entering hour numbers in time sheet calculator

im using excell 2007 ; )

Originally Posted by wenqq3

You can key number at A2 without :, then the time format will show at below formula.

Please Login or Register  to view this content.

thanks wenqq3 , tried and worked as wanted just one problem it gives all the hours in AM how can i state with the formula or somehow if it is AM or PM ????

Originally Posted by wenqq3

You can key number at A2 without :, then the time format will show at below formula.

Please Login or Register  to view this content.

thanks wenqq3 , tried and worked as wanted just one problem it gives all the hours in AM how can i state with the formula or somehow if it is AM or PM ????

like the attached example the person enters at 9 am out to lunch at 12 pm back from lunch at 1-45 and out of work at 7- pm , and want to use standard hour time to enter the data i dont want to use military hour to enter the time

hello kevin i know that would make the formula work , but the case is that the persons that could use the program want it easy and dont want to enter in military time cuz that would mean to "translate" military time to regular time and want to make it as easy as posible if it can

just cheked it kevin but that would mean enter the time in military format which is what i dont want , i want to enter the time in regular hour format , if it is not posible is there a way i can enter a PM with the time so the formula converts the time to pm with out using a AM / PM column ?????

Originally Posted by wenqq3

If not use military hour, excel won't know it is AM/PM unless you type AM/PM your self.
Suggestion, there is 2 column (AM & PM),if 7am then enter at column AM. if 815pm then enter at column PM.

is there a way i can enter a PM with the time so the formula converts the time to pm with out using a AM / PM column ?

that would be entered by the person as 5:00 pm ( thats the out of work hour )

Originally Posted by Tony Valko

See this:

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

checked it but that would involve vba programing and would like with some type of formula in case have to make changes be easier : /

Originally Posted by Kevin UK

So if you enter the time as 5:00 pm, excel recognises that as time!

this is for example what i want to enter

645 and want the excel to display 6:45 am

1207 and want the excel to display 12:07 pm

128 and want the excel to display 1:28 pm

500 and want the excel to display 5:00 pm

Originally Posted by wenqq3

I just see you had attach a file.
One question, your worksheet wish to calculate the "Total Hours", or just wanna show AM/PM with typing (1 to 12 only) at column B, C, D and E?

hello wenqq3 i want as you say i want to show AM/PM with typing (1 to 12 only) at column B, C, D and E

in the atachment of test2 the space highlited in yeloow is where i want the numbers to go with the am/pm test2.xlsx

Originally Posted by tecnico

in the atachment of test2 the space highlited in yeloow is where i want the numbers to go with the am/pm Attachment 233682

if its not to much to ask can you enter the formula in those cells ? ive tried but gives me an error

Originally Posted by wenqq3

Okie, i think the formula TIME(TRUNC(A2/100),MOD(A2,100),0) can't applied in your worksheets.
Why don't you type in time + (am/pm) then i can do formula in F G H to calculate the hours.

Kevin UK, any idea with tecnico problem? I dont think excel can recognize AM/PM if we no typing AM/PM or military time.

ok wenqq3 can you do it in the worksheet ? example is there a way i can enter 545 pm or something like that to get 5:45 pm in b, c, d or e ? in other words what i want if posible is to enter the data without colon to speed up and if posible enter either am or pm to difrence the hour data , would apreciate if you can do that on the worksheet ciz i dont find a way to do it

ok look at this worksheet atached i entered the data , so you can calcualte the colums f, g, h , thanks in advanced

Originally Posted by Kevin UK

Here you go. See if this is of any use. Keep your start times in column B and your finish times in column E.
You might need to do some rounding, but I will leave that to you.

Perfect kevin that is what i wanted , and also thanks to wenqq3 because the option of using the military hour i can use in another worksheet , thanks to both