When working with Microsoft Excell....?

Question

I am putting times in a column (ex. 12:45am) in this format. If I want to total all a.m.'s and p.m.'s in this column, how should i format the cell?

For example:
12:45am
1:37pm
3:45am
4:30pm
..on and on....

Total am: ____
Total pm: ____


Without me having to count each cell, how can I total the entire column into 2 seperate sums???

Thank you

Answer 1

Do a formula in the row next to it IF(right,CELLNAME(2),"a",1,0)

Then copy that down the collumn. This will give you a marker for all of the am's.

Then use the next collumn over and use IF(right,CELLNAME(2),"p",1,0)

copy it down and it will give you a marker for all the pms. sum the markers and you are done.

I am assuming the times were brought in as text.

Answer 2

To get the cell to show exactly what you are typing in (if a format doesn't exist) you would want to format it as "Text." It will treat it as if you were typing a sentence.

As far as getting a sum of a column or selection, use the Sigma (sum) button. It's the one that looks like a greek-ish capital letter "E"

As far as getting it to give you a sum of time periods...I couldn't tell you how to do that.

Adding up a series of single times won't give you any information, except maybe an average time if you then.

Adding 10:30AM and 1:00PM doesn't tell me anything. adding 10:30AM-11:22AM and 1:00PM-1:13PM gives me a total time of 1 hour and 5 minutes, or 65 minutes. If you're looking to get a total time, you would need a duration...meaning a start and end time (which would then give you a total number of minutes) or a total number of minutes, which you could then add together.

Answer 3

Type in the following functions in the columns you want the totals to appear in --

Function to use for Total am:
=Countif(A1:A100<11:59am)

Function to use for Total pm:
=Countif(A1:A100>12:00pm)

This is the COUNTIF function, which basically says 'Count if cells in A1 to A100 are less than 11:59am' and 'Count if cells in A1 to A100 are greater than 12:00pm'

You wouldn't actually use A1:A100 -- those just represent the cells that you want a total for. Replace those cell references to with the actual cells you want a total for.

Answer 4

Highlight the cells you wish to have the am/pm added to.

Right click the highlighted cells.

Select "Format Cells"

Under the number tab, click "Time"

Select the format you wish to use, and it will automatically addt he AM/PM for you.