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.
