Excel - IF Function Problem - Expert Solution

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.