formulas in exel

  kevc 20:18 20 Apr 03
Locked

i dont suppose you know anything about formating cells in exel. I want to add two formulas in one cell. Is this possible. and how do i do it. One of the formulas is to auto calculate for example in a1 which is day 1 the finish time is 18.00. in b1 which is day 2 the start time is 9.06, so how would i write the formula to calculate the total time in between these times and the other formula is to add hours to a monthly total. this is why i need both formulas in the cells.

  VoG™ 22:09 20 Apr 03

You can only have one formula per cell.

If you format three cells as times then you can e.g. enter start time in A1, finish time in B1 and in C1 enter

=B1-C1

to get your monthly total you need an extra column, say D. In D1 enter

=sum($C$1:C1)

Hover the cursor over the bottom right corner of D1 and it will turn into a +

Keep the left hand mouse button pressed and pull the mouse down to copy this formula down as far as you need to. If you then have a look you will see e.g. in D2 the copied formula has become

=sum($C$1:C2)

in other words it will keep a running total.

  VoG™ 22:10 20 Apr 03

Sorry, in C1 enter

=B1-A1

  LastChip 22:19 20 Apr 03

If you format cell's a1, b1 & c1 with the "Date/Time" function, now in c1 place the formula;

=b1-a1

you will end up with the day and time difference in c1.

Now just for clarity, move on to the "E" column and after formatting to date/time in the same manner, in "e1" write;

=c1

Carry on the second row in exactly the same fashion, but; =b1-a1, becomes =b2-a2 and when you get to the e2 cell, the formula becomes;

=C2+E1

This will give you an addition to the previous total.

If you were to work your way down the sheet each row increases the value by one, so a2 becomes a3 and so on. =C2+E1, becomes =C3+E2.

I am sorry if I am teaching you to "suck eggs", but is this what you wanted?

The only other method I can think of, is to write a macro for Excel, but I am afraid that is beyond my skills. You need someone with programing knowledge to help you with that.

  tbh72 01:17 21 Apr 03

You must be getting the MONTHLY TOTAL from somewhere else?? Have you considered defining a name for the monthly total..... You could then do a formula based on the info supplied above as...


=MonthlyTotal+(B1-A1)

Hope that makes sense

  Megatyte 11:11 21 Apr 03

The above formula =B1-A1 will not work when the period spans over midnight. The correct formula is

=IF((B1-A1)<0,24+(B1-A1),(B1-A1))

where A1 is the finish time on day 1 and B1 is the start time on day 2.

AH

  Megatyte 11:51 21 Apr 03

The monthly total formula will not work either, it rolls over every 24 hours so 13+13+13 will give you the result of 15. I'll do a formula for that later but have to go out now(the pubs are open!)

AH

  Megatyte 16:02 21 Apr 03

"and the other formula is to add hours to a monthly total"

Could you explain this in a bit more detail?

AH

  LastChip 16:14 21 Apr 03

When I tested what I had written above in Excel, When passing over midnight, the result I got was "x" days and "y" hours so to speak, but only if the cell (or column) was formatted as a Date/Time combination.

However, my knowledge is very limited in this field, and if Megatyte can help you further; great!

  VoG™ 00:21 22 Apr 03

Hello?

  Megatyte 02:28 22 Apr 03

???

This thread is now locked and can not be replied to.

How to get Windows 10 for free | How to install Windows 10: There is still a way to avoid paying…

1995-2015: How technology has changed the world in 20 years

Alex Chinneck’s giant ice cube Christmas tree at Kings Cross

Apple rumours & predictions 2017: The iPhone 8, new iPads, and everything else you should expect fr7…