Hi all, I'm creating a very quick and simple excel sheet for HR (they didn't want to use a full program for this purpose, (as it's just for some contract work for someone) for Time Keeping.

I'm having issues creating a formula to sum time. I can figure it out if it's in military time based off what I Googled. but I can't seem to figure it out to add in standard time (which was requested) with AM & PM.

Can any excel masters lend a hand?

I have example data in the Excel document already. Basically I need the total hours summed up for the periods automatically above. Then down below it would be cool if the total hours stopped at 40 once reached then it would show the amount of overtime after that?

Any advice would be welcome. Thank you in advance.

Hi Doyle1,

Here's the attached Excel and how I did it for the following:

I used conditional formatting to format the total hours to display 40 hours when it's greater than 40.

For the following columns, I used this formula to calculate the time.

**Column D**

```
=(C4-B4+(C4<B4))*24
```

**Column G**

```
=(F4-E4+(F4<E4))*24
```

## 5 Replies

Hi Doyle1,

Here's the attached Excel and how I did it for the following:

I used conditional formatting to format the total hours to display 40 hours when it's greater than 40.

For the following columns, I used this formula to calculate the time.

**Column D**

```
=(C4-B4+(C4<B4))*24
```

**Column G**

```
=(F4-E4+(F4<E4))*24
```