A Cumulative formula is needed for tracking positive and negative times for a flexi balanc

Hello

First time poster. I have been scouring the Internet and this website for a solution but I haven't found one yet.

I have a column in Excel containing positive and negative values in the format of hours and minutes. It's a formula that calculates if more or less than 7 hours have been worked in a day.

The starting flexi balance for a month is zero hours and zero minutes, displayed as 0:00. A full day's work is 7 hours.

Let's say on day one I work 6:45, it would result in the flexible balance showing as negative 15 minutes, -0:15. And if I worked an extra half hour on the 2nd day it should show a new flexi balance of 15 minutes in the format of 00:15.

I am struggling to create a Cumulative formula that can change to either a positive or negative value, without just showing ### when the value is a negative.

I am already aware of using the 1904 date system but that isn't an option.

Any help would be greatly appreciated.

Thanks

Billy

Are you really still using Excel 2007??

Your sample is incomplete, as you haven't calculated hours worked and compared them with a standard day. You just start by adding a negative time. That won't work.

Take a look at this file.

The flexi-time column is TEXT that looks correct. The total at the foot of the data therefore has to convert text back into numbers.

If you want solutions for O365, then amend your profile to show O365...

Actually, the forum does NOT force you to upload anything. However, a sample sheet makes it much easier to follow what you want. Words are often VERY ambiguous.

My view is that because we do this for fun... and because you want free help... we expect YOU to put in a bit of effort to help us to help you. Often that means the provision of a >>>representative<<< sample sheet.

1. I do not consider my reply at all aggressive.

2. The forum platform is elderly and a bit flakey. Sometimes (all too often) things go wrong. However, there is absolutely no mandatory requirement for attachments to be uploaded.

3. I do not see the word "demand" in my previous reply. Where did you see it?

Thank you for your complaints and negative reputation.

i hope the help offered at Post 2 worked for you.

Can you confirm which version of Excel you are now using?

It's in your profile - could you please update this to Excel 2016 (and not show 2013)?

I am going to need a more detailed workbook, please. I think you are saying that the values come from a formula - is this correct? Also you talk about a starting balance, but I don't see this anywhere. I'd like to see a bit of wider context in order to be able to give the best advice. Does this make sense?

Hello

Sorry for the confusion, my work environment is awful and has folders that show MS Office 2013 as well as MS office 2016, so I must be using office 2016.

Yes what you have said makes sense. I will need to try to create another spreadsheet and upload it for you so my request makes more sense. I unfortunately can't send the spreadsheet outside of my organisation due to GDPR.

What I am trying to achieve is a spreadsheet that can keep a running total of accrued flexible working hours. The running total needs be able to show negative values as well as positive values.

The starting flexi Balance for a month is displayed as 00:00. A full day's required work is 7 hours, displayed in a seperate cell as 7:00.
If on the first day I work 6 hours, I would expect the flexi Balance tracking cell to show as negative 1 hour, "-01:00", and if I was to work an extra 2 hours the following day, I would expect the flexi tracker cell on the row beneath to show as "01:00".

Hopefully the above makes sense.

Thank you.

Great - I'll wait for the more detailed (but desensitised) sample workbook with the formulae that you are using in place. I understand the requirement, I just need more detailed data to work with. Please don't forget to add a MANUALLY calculated column showing your expected results. Thanks.

Named Range ("Daily") - CTRL-F3 to view/edit
=SUM(IFERROR(TIMEVALUE(Sheet1!$L$7:$L7),0))-SUM(IF(LEFT(Sheet1!$L$7:$L7,1)="-",SUBSTITUTE(Sheet1!$L$7:$L7,"-","")+0,0))


L7:
=IF(I7="","",IF(I7>J7,TEXT(SUM(I7-J7),"-[hh]:mm:ss"),TEXT(SUM(J7-I7),"[hh]:mm:ss")))


M7:
=IF(Daily<0,TEXT(ABS(Daily),"-[hh]:mm:ss"),TEXT(Daily,"[hh]:mm:ss"))

The formula in M7 >>may<< be an array formula in your older Excel product. If it appears in the file inside a set of {} it needs to be set with CTRL-SHIFT-ENTER in M7, before dragging down. It might be OK as is... I just can't remember if Named Ranges are calculated as arrays in older Excel products.

Thank you for that.

The named range formula will need to be amended however as the name of the working tab needs to change each month, so the current tab I am working with is named "November 2023", future months will use the same naming format. The tabs are only created after the previous month has ended.

Is there any way to amend the formula so that it applies to all workbooks rather than a particular one?

Thankyou

I won't be able to do anything further until tomorrow pm. I'm on my way to Dublin for a concert and have no PC with me.

One thought you can try...

Copy the named range formula (minus the =) and paste it into the formula in M7 at every place where Daily is present. It'll be a bit of a monster, but will be more easily applicable on multiple sheets.

After a few hours of rebuilding my works master spreadsheet and using those formulas you suggested I did eventually get it working.

The cumulative formula didn't like me placing the named range formula in it where 'Daily' was but after I created a seperate column just for the range formula the running flexible working balance formula started to work correctly.

For all the columns, I did need to use the CTRL-SHIFT-ENTER option to copy and paste the formulas to the bottom of the spreadsheet.

Hopefully this will help others facing the same issues.

I can't believe how unnecessarily difficult Excel makes managing negative time values.