Oct 08 2019 07:48 AM
I am trying to set up a simple formula so that I can track the amount of leave hours I've accumulated each month.
I have a spreadsheet that tracks my work activities (that someone else created). Each month is a different tab and I already track how many hours of leave I use each month. The total leave used for the month is cell C50 and is formatted using the custom number formatting of [H]:mm;@ so that it will display like this 15:30 (15 hours and 30 minutes used in January) . If I know I'm starting with 100 hours and I will add 14 hours every month how do I set up my formula?
It seems easy enough Starting in January in I have 100+14-the value in cell C50 then in Feb I should just take the value from January's leave balance + 14 - the value in the cell C50 and so on. But I can't figure out how to get this to work. I suspect you have to do something specific because I'm working with time values as my formula =(100+14)-C50 gives me a result of 2720:30 which makes no sense to me as the end result should be 98 hours and 30 minutes.
Can someone please help me with this?
Oct 08 2019 07:55 AM
In Excel one day is integer 1 and an hour is 1/24. Thus your formula shall be like
=(100+14)/24-C50
formatted as [hh]:mm