Need help in creating a formula
 

I need to create a spreadsheet where I take daily totals (mon-fri x 4 weeks)
and come up with a billing total of hours. For instance we charge $1.75 for
a half hour and $3.50 an hour for care. I need to be able to put in
something like 3.5 on a mon and say 2.0 hours Tues etc. and come up with a
grand monthly total of hours and dollar amounts to go with that. Any
thoughts?

Need help in creating a formula
 

I have 5 time values in A1:E1
2:10 3:45 6:34 5:37 4:15

I can sum these and convert to decimal hours with =SUM(A1:E1)*24
But to round to the nearest 1/2 hour I use =ROUND(SUM(A1:E1)*24*2,0)/2
The result is 22.5 but please note the cell must be formatted General (Excel
will want to format it Time)
In G1 I compute the fee using =INT(F1)*3.5+MOD(F1,1)*1.5 with result $77.75
Not that if the total time is, say, 22:10 then I get no money for the 10
mins
We can change that if you wish with =CEILING(SUM(A1:E1)*24,0.5) in F1
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"jeannie" wrote in message
...
I need to create a spreadsheet where I take daily totals (mon-fri x 4
weeks)
and come up with a billing total of hours. For instance we charge $1.75
for
a half hour and $3.50 an hour for care. I need to be able to put in
something like 3.5 on a mon and say 2.0 hours Tues etc. and come up with a
grand monthly total of hours and dollar amounts to go with that. Any
thoughts?

Need help in creating a formula
 

Thanks for helping...
This could help clarify my situation better:
Time starts for billing at 3:15p and goes till 6:15p
We bill automatically $1.75 for 30 and or $3.50 for an hour. So, we'd
always be putting in everything at the half hour or hour rate. There is no
minute billing. The most that could be charged would be $10.50. Does that
help?

"Bernard Liengme" wrote:

I have 5 time values in A1:E1
2:10 3:45 6:34 5:37 4:15

I can sum these and convert to decimal hours with =SUM(A1:E1)*24
But to round to the nearest 1/2 hour I use =ROUND(SUM(A1:E1)*24*2,0)/2
The result is 22.5 but please note the cell must be formatted General (Excel
will want to format it Time)
In G1 I compute the fee using =INT(F1)*3.5+MOD(F1,1)*1.5 with result $77.75
Not that if the total time is, say, 22:10 then I get no money for the 10
mins
We can change that if you wish with =CEILING(SUM(A1:E1)*24,0.5) in F1
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"jeannie" wrote in message
...
I need to create a spreadsheet where I take daily totals (mon-fri x 4
weeks)
and come up with a billing total of hours. For instance we charge $1.75
for
a half hour and $3.50 an hour for care. I need to be able to put in
something like 3.5 on a mon and say 2.0 hours Tues etc. and come up with a
grand monthly total of hours and dollar amounts to go with that. Any
thoughts?

Need help in creating a formula
 

Tell us what cells have what data
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"jeannie" wrote in message
...
Thanks for helping...
This could help clarify my situation better:
Time starts for billing at 3:15p and goes till 6:15p
We bill automatically $1.75 for 30 and or $3.50 for an hour. So, we'd
always be putting in everything at the half hour or hour rate. There is
no
minute billing. The most that could be charged would be $10.50. Does
that
help?

"Bernard Liengme" wrote:

I have 5 time values in A1:E1
2:10 3:45 6:34 5:37 4:15

I can sum these and convert to decimal hours with =SUM(A1:E1)*24
But to round to the nearest 1/2 hour I use =ROUND(SUM(A1:E1)*24*2,0)/2
The result is 22.5 but please note the cell must be formatted General
(Excel
will want to format it Time)
In G1 I compute the fee using =INT(F1)*3.5+MOD(F1,1)*1.5 with result
$77.75
Not that if the total time is, say, 22:10 then I get no money for the 10
mins
We can change that if you wish with =CEILING(SUM(A1:E1)*24,0.5) in F1
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"jeannie" wrote in message
...
I need to create a spreadsheet where I take daily totals (mon-fri x 4
weeks)
and come up with a billing total of hours. For instance we charge
$1.75
for
a half hour and $3.50 an hour for care. I need to be able to put in
something like 3.5 on a mon and say 2.0 hours Tues etc. and come up
with a
grand monthly total of hours and dollar amounts to go with that. Any
thoughts?

Need help in creating a formula
 

My spreadsheet contains a month of weeks
exp.

GR STUDENT MON 1 TUE 1 WED 1 THU 1 FRI 1 TOTAL 1

I have this across the top but as the weeks increase it becomes Mon 2, Mon
3, Mon 4. You get the idea. Then at the end I have a Total Weeks which is a
sum total of total 1, total 2, total 3 and total 4. I've got all this
working. My problem now is getting the total weeks column converted over to
a dollar amount figure.

I will never key in anything but half hour to hour increments of time. Such
as 2.5 would represent 2 1/2 hours of billed time. At a rate of $3.50 and
hour.

Does this help?



"Bernard Liengme" wrote:

Tell us what cells have what data
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"jeannie" wrote in message
...
Thanks for helping...
This could help clarify my situation better:
Time starts for billing at 3:15p and goes till 6:15p
We bill automatically $1.75 for 30 and or $3.50 for an hour. So, we'd
always be putting in everything at the half hour or hour rate. There is
no
minute billing. The most that could be charged would be $10.50. Does
that
help?

"Bernard Liengme" wrote:

I have 5 time values in A1:E1
2:10 3:45 6:34 5:37 4:15

I can sum these and convert to decimal hours with =SUM(A1:E1)*24
But to round to the nearest 1/2 hour I use =ROUND(SUM(A1:E1)*24*2,0)/2
The result is 22.5 but please note the cell must be formatted General
(Excel
will want to format it Time)
In G1 I compute the fee using =INT(F1)*3.5+MOD(F1,1)*1.5 with result
$77.75
Not that if the total time is, say, 22:10 then I get no money for the 10
mins
We can change that if you wish with =CEILING(SUM(A1:E1)*24,0.5) in F1
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"jeannie" wrote in message
...
I need to create a spreadsheet where I take daily totals (mon-fri x 4
weeks)
and come up with a billing total of hours. For instance we charge
$1.75
for
a half hour and $3.50 an hour for care. I need to be able to put in
something like 3.5 on a mon and say 2.0 hours Tues etc. and come up
with a
grand monthly total of hours and dollar amounts to go with that. Any
thoughts?

Need help in creating a formula
 

Assuming your spreadsheet has total1 in column H, total2 in column N total3
in col T and total4 in col Z and your need you total in row 2. also assume
monthly total is in col AA

in cell AA2 place formula
= (H2+N2+T2+Z2)*3.5



"jeannie" wrote:

My spreadsheet contains a month of weeks
exp.
A B C D E F G H
GR STUDENT MON 1 TUE 1 WED 1 THU 1 FRI 1 TOTAL 1

I have this across the top but as the weeks increase it becomes Mon 2, Mon
3, Mon 4. You get the idea. Then at the end I have a Total Weeks which is a
sum total of total 1, total 2, total 3 and total 4. I've got all this
working. My problem now is getting the total weeks column converted over to
a dollar amount figure.

I will never key in anything but half hour to hour increments of time. Such
as 2.5 would represent 2 1/2 hours of billed time. At a rate of $3.50 and
hour.

Does this help?



"Bernard Liengme" wrote:

Tell us what cells have what data
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"jeannie" wrote in message
...
Thanks for helping...
This could help clarify my situation better:
Time starts for billing at 3:15p and goes till 6:15p
We bill automatically $1.75 for 30 and or $3.50 for an hour. So, we'd
always be putting in everything at the half hour or hour rate. There is
no
minute billing. The most that could be charged would be $10.50. Does
that
help?

"Bernard Liengme" wrote:

I have 5 time values in A1:E1
2:10 3:45 6:34 5:37 4:15

I can sum these and convert to decimal hours with =SUM(A1:E1)*24
But to round to the nearest 1/2 hour I use =ROUND(SUM(A1:E1)*24*2,0)/2
The result is 22.5 but please note the cell must be formatted General
(Excel
will want to format it Time)
In G1 I compute the fee using =INT(F1)*3.5+MOD(F1,1)*1.5 with result
$77.75
Not that if the total time is, say, 22:10 then I get no money for the 10
mins
We can change that if you wish with =CEILING(SUM(A1:E1)*24,0.5) in F1
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"jeannie" wrote in message
...
I need to create a spreadsheet where I take daily totals (mon-fri x 4
weeks)
and come up with a billing total of hours. For instance we charge
$1.75
for
a half hour and $3.50 an hour for care. I need to be able to put in
something like 3.5 on a mon and say 2.0 hours Tues etc. and come up
with a
grand monthly total of hours and dollar amounts to go with that. Any
thoughts?