Timesheet formulae

  • Thread starter Thread starter JoanC
  • Start date Start date
J

JoanC

I need to know how to calculate times of attendance when such times can
carry over many days with no time off. I have tried date and time in
the same formula but only the hours seem to formulate. As far as I am
aware, the date is NOT as text and the time is 24 hour clock. E.G:
A B
C D
1 Joan 1/01/2004 09:00:00 5/01/2004 12:00:00

(These dates are the Australian format.)

I now need to total these hours in column D: what formula do I use?
 
This simplified reply gives a total of 3 hours and adds an irrelevant
date when the amount looked at should be somewhere in the vicinity of
99 hours. This formula is obviously not taking the days (over 4 days)
into account. Is there a formula that will achieve this?

Attachment filename: timesheetproblema.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=419549
 
That had to be a typo, it should be a custom format of
[hh]:mm
the square brackets keep the hours from overflowing into days.


Peo Sjoblom said:
=C-B

custom format as [hh:mm]
Click to expand...
 
This still will not work. My program will not allow the addition o
square brackets in the format cells window. The enclosed spreadshee
shows the first version and the suggestion by you. The verson of Exce
I am using is XP 2002. Does this version perhaps not allow what I a
trying to do

Attachment filename: timesheetproblema.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=41955
 
I guess you don't understand what a custom format is, select D2, do
format>cells>number and select custom,
then put [hh]:mm in there. I downloaded your wb and the first formula
returned 99:00 and the second 78:00
 
I only just looked at this message and I can see theres been quite a few
replies already. The [hh]:mm worked for me but I just wondered if you couldnt
do 24*(D1-C1) - it returns 99.

It doesnt return minutes obviously but if the staff a re paid hourly it
shouldnt matter. I havent actually looked at your spreadsheet yet - Ill have to
do that

Jonathan B
 
It will return minutes although they will be decimal minutes, i.e.

99:20 will be returned as 99.3333333333333
Obviously if the OP wants to get a pay amount that's the way to go.
 
Thank you very much for your assistance. It helped a lot once I
realised I was formatting the wrong field. I was trying to set up the
format in the B & C fields. Apparently the days look after themselves
while the hours can cause the problems. Again thank you.
 
There have been hundreds of postings on this NG on doing arithmetic with
times, but this is the first time I've seen the square bracket format
mentioned. It solves a lot of problems.

It took me a long time to find it in XL's so-called Help, and no doubt would
have taken even longer if I hadn't done what many users do and killed off
that worse-than-useless paperclip.

Perhaps while we're waiting for the provision of a usable Help system, a
friendly MVP could give us a rundown on this feature?

Regards


Peo Sjoblom said:
I guess you don't understand what a custom format is, select D2, do
format>cells>number and select custom,
then put [hh]:mm in there. I downloaded your wb and the first formula
returned 99:00 and the second 78:00
Click to expand...
 
Hi Chris,
You've eliminated Clippy so you can use/see HELP better.
You've read the HELP.
You've changed your code.
Your code works.
What more is there to say.

I usually add something like the square brackets keep the hours
from rolling up into days, but they that was in this thread as well.

I have a page that includes *other* custom formats that might be
of interest. Any formatted cell can be looked at with Format, cells, custom
to see what the format used actually is and you can use my
GetFormula and GetFormat user defined functions to display formula or format
of a cell from another cell.
http://www.mvps.org/dmcritchie/excel/formula.htm#GetFormatExample

I have a webpage on date and time
http://www.mvps.org/dmcritchie/excel/datetime.htm
as does Chip Pearson
http://www.cpearson.com/excel/datetime.htm


Chris R. Lee said:
There have been hundreds of postings on this NG on doing arithmetic with
times, but this is the first time I've seen the square bracket format
mentioned. It solves a lot of problems.

It took me a long time to find it in XL's so-called Help, and no doubt would
have taken even longer if I hadn't done what many users do and killed off
that worse-than-useless paperclip.

Perhaps while we're waiting for the provision of a usable Help system, a
friendly MVP could give us a rundown on this feature?

Regards


Peo Sjoblom said:
I guess you don't understand what a custom format is, select D2, do
format>cells>number and select custom,
then put [hh]:mm in there. I downloaded your wb and the first formula
returned 99:00 and the second 78:00

--

Regards,

Peo Sjoblom

JoanC > said:
This still will not work. My program will not allow the addition of
square brackets in the format cells window. The enclosed spreadsheet
shows the first version and the suggestion by you. The verson of Excel
I am using is XP 2002. Does this version perhaps not allow what I am
trying to do.

Attachment filename: timesheetproblema.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=419559
Click to expand...
Click to expand...
Click to expand...
 
Hi Joan,

I think what Peo is trying to tell is: if you right click on the cel
with the formula, then click on format cell. Once the box appear
scroll down to custom and the box will open up that you have a box tha
you can type in [hh] once you type this in it places into the box whic
will allow you choose it as though it has always been there. See th
attached.

HTH,

Kathy Ra

Attachment filename: formatting_cell.gif
Download attachment: http://www.excelforum.com/attachment.php?postid=42113
 
Back
Top