formulas in exel

  kevc 20:18 20 Apr 2003

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 2003

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


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


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


in other words it will keep a running total.

  VoG™ 22:10 20 Apr 2003

Sorry, in C1 enter


  LastChip 22:19 20 Apr 2003

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


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;


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;


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 2003

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...


Hope that makes sense

  Megatyte 11:11 21 Apr 2003

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


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


  Megatyte 11:51 21 Apr 2003

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!)


  Megatyte 16:02 21 Apr 2003

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

Could you explain this in a bit more detail?


  LastChip 16:14 21 Apr 2003

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 2003


  Megatyte 02:28 22 Apr 2003


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

Elsewhere on IDG sites

Best Black Friday Deals 2017

Black Friday Deals for Designers & Artists: Adobe, Apple, Corel Painter, Microsoft Surface, Wacom &e…

Best Black Friday Apple Deals 2017

Black Friday 2017 : date, sites participants & bonnes affaires