**Question description:**

*This user has given permission to use the problem statement for this blog.*

i need a formula that I can use to calculate hours for time in lieu, an example would be if I worked on extra on a certain day , I want to take those hours worked and add 4 hours to it, then at the end of the week, I want to be able to add all those hours up in total.
so an example would be worked 3 hours extra when I am on call, I am paid the 3 hours plus an additional 4 hours for being called in.

Solved by M. C. in 60 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
13/04/2018 - 08:39

hello

User
13/04/2018 - 08:39

hi there

Excelchat Expert
13/04/2018 - 08:40

can you tell me some more details?

Excelchat Expert
13/04/2018 - 08:40

or the file?

User
13/04/2018 - 08:42

ok, using the sheet displayed, the table was a calendar month, I work shifts and I am also rostered for on call work, if I am called in, I book my hours of attendance and I am also given an extra 4 hours to compensate for being called in, so I would like a formula that when I input a number for example 3, then it would automatically add 4 hours to this number and a the end of the week or month would give me a total of hours I have accrued

Excelchat Expert
13/04/2018 - 08:43

which sheet?

User
13/04/2018 - 08:45

any excel spreadsheet, I am creating a spreadsheet showing my shift rota for a year so I know when I am working and when I am rostered off, when I am on call and when I am not. so when I am on call, I want to be able to add the number of hours I was called in for into the spreadsheet and for the formula to add 4 hours onto this, then at the end of the month to give me a total of the number of hours I have worked including the extra 4 hours each time I have been called in

User
13/04/2018 - 08:47

for example, I am working a shift until 6pm tonight and then at 8pm I was called back into work, and I was on site for 3 hours, I need a formula that would let me put the number 3 into the call for today and then automatically add 4 hours onto this. and at the end of the calendar month it would totalise all the cells for that month and give me a total of hours accrued.

Excelchat Expert
13/04/2018 - 08:48

it would be better if you can share the file

User
13/04/2018 - 08:48

i have not created the file yet.

Excelchat Expert
13/04/2018 - 08:49

ok then lets create one in the shared view

User
13/04/2018 - 08:49

but it would just be a standard excel sheet, that showed months and dates for that month.

Excelchat Expert
13/04/2018 - 08:49

https://docs.google.com/spreadsheets/d/1BbOXc4BHLa5T99jnYMocwtAs8crkKDA6NWJkuB3ca5g/edit?usp=sharing

User
13/04/2018 - 08:52

i do not need an hourly breakdown of the day, i just need to know today is Friday 13th april and for example i worked 3 hours on call and so i want the cell for today to show the number 7 and then at the end of the month to tell me how many hours i have by adding up all the cells that have number in it, that bit i could do, but i do not know the formula for if a cell contains a number, then add that number to 4 to give a sum, then add all the cells numbers into one box so i have running total

Excelchat Expert
13/04/2018 - 08:55

Ok

User
13/04/2018 - 08:55

it would also need to accept part numbers, for example i worked 3 hours and 15minutes and for it to the total that as 7 hours and 15mins

Excelchat Expert
13/04/2018 - 08:58

check will this work.

Excelchat Expert
13/04/2018 - 08:59

?

User
13/04/2018 - 08:59

forget normal duty times, i just need the cell to contain a formula that if it detects a number or a part of a number showing a quarter or half an hour that it takes that number, adds 4 to it and gives me a total, then at the end of the month gives me a total...

Excelchat Expert
13/04/2018 - 09:01

can you write on the shared sheet

Excelchat Expert
13/04/2018 - 09:01

show me what you need?

User
13/04/2018 - 09:04

i have made notes on the cells and what i need

Excelchat Expert
13/04/2018 - 09:05

ok

Excelchat Expert
13/04/2018 - 09:05

so cell beside should be used to add 4?

User
13/04/2018 - 09:05

all i need is a formula that detects a number ie 3 or a part number 3hours 15mins (3.15) to add the number 4 onto it and give me either 7 or 7.15 and at the end of the month to give me a running total

User
13/04/2018 - 09:06

no i want the cell i have entered for example 3.15 into to show me 7.15 and not what i typed.

User
13/04/2018 - 09:08

i have added numbers on to the sheet and it has totalled the numbers, but it has not added the extra 4 hours per entry, so it is 12 hours short

Excelchat Expert
13/04/2018 - 09:08

that is not possible that you write something in a cell but in that cell will show different thing

Excelchat Expert
13/04/2018 - 09:09

but yes it might be possible to add the extra 4 in the sum

User
13/04/2018 - 09:10

i do not want to have extra cells filled with numbers

Excelchat Expert
13/04/2018 - 09:11

now check

User
13/04/2018 - 09:13

i that is perfect

Excelchat Expert
13/04/2018 - 09:13

:)

Excelchat Expert
13/04/2018 - 09:14

one thing

Excelchat Expert
13/04/2018 - 09:14

there is a problem with 3 hour 15 min....it should not be 3.15

User
13/04/2018 - 09:14

what should it be

Excelchat Expert
13/04/2018 - 09:15

it should be 3+(15/60) hours

Excelchat Expert
13/04/2018 - 09:16

use the cell G2 and H2 to have the exact hour vale

Excelchat Expert
13/04/2018 - 09:16

value

User
13/04/2018 - 09:16

how do i have then 3 hours and 15minutes in the same cell?

Excelchat Expert
13/04/2018 - 09:17

there is no easier way then this....enter your hour and minute in G2 and H2...look what actual hour value is...then copy paste that value

User
13/04/2018 - 09:18

ok, so i have to either enter for example, 3.25, 3.5 or 3.75 as my entry to the cell, as i book 15minute times

Excelchat Expert
13/04/2018 - 09:19

yes

User
13/04/2018 - 09:19

so if i work 3 hours and 45 minutes, then i would put 3.75 into the cell

Excelchat Expert
13/04/2018 - 09:19

exactly

User
13/04/2018 - 09:19

thanks

Excelchat Expert
13/04/2018 - 09:19

most welcome

User
13/04/2018 - 09:20

have a good day and thank you very much for solving this problem for me.

Excelchat Expert
13/04/2018 - 09:24

most welcoem

Excelchat Expert
13/04/2018 - 09:24

*most welcome

Excelchat Expert
13/04/2018 - 09:24

have a nice day to you too

Excelchat Expert
13/04/2018 - 09:24

if you leave please rate me after ending the chat

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*