Timesheet minus lunch (PLEASE HELP!!!!)

[Jaffa]

MonS - Monday Start Time (Date/Time)
MonF - Monday Finish Time (Date/Time)
MonL - Monday lunch duration (Number, Long Integer)

I currently have: HoursWorked: (DateDiff("n",[MonS],[MonF])-Nz([MonL],0))/60

If I enter the following values in the query:
MonS - 8.30
MonF - 17.00
MonL - 45 mins

I get 7.75. I need it to show 7.45
Can someone please help me with this!
Thanks!

 

[Philip Herlihy]

MonS - Monday Start Time (Date/Time)
MonF - Monday Finish Time (Date/Time)
MonL - Monday lunch duration (Number, Long Integer)

I currently have: HoursWorked: (DateDiff("n",[MonS],[MonF])-Nz([MonL],0))/60

If I enter the following values in the query:
MonS - 8.30
MonF - 17.00
MonL - 45 mins

I get 7.75. I need it to show 7.45
Can someone please help me with this!
Thanks!

I think you just need to format the result as a "Time" instead of a
number! In query design mode, select the field, right-click and pick
"Properties". Select the "short time" format for the field and you're done.

Phil, London

 

[Steve Sanford]

WHY do you need it to show 7.45???? 7.45 is the WRONG answer. 7.75 is
the correct answer.

From your field names, it looks like you are committing "Spreadsheet".
Forgetting this, you have three fields, two of which are date/time types.

If you start work at half past eight in the morning, then you CANNOT enter
the time as 8.30!!! It MUST be entered as 8:30. 8.3 is 8 hours and 18
minutes.

Likewise, if you quit at five PM, then the ending time MUST be entered as
17:00! Note the colon seperator.

If the lunch break is 45 minutes, then the total time worked would be 7.75
hours. Spelled out, you would say someone worked 7 hours and 45 minutes!!

7.45 hours is equal to 7 hours and 27 minutes. You CANNOT write it as
7:45.... That is a TIME reference!


HTH

 

[CraigH]

Well I think Philips answer won't work in a query.

And Steve just doesn't answer what you really want. He is also wrong about
the 8.3 it will enter as 8:03 and 8.30 will enter as 8:30 - try it and see.
He is right about your "Spreadsheet" aproach and that Is why I changed the
names.

But to get what you want and not how you said it - 7:45 showing hours and
minutes it is this

TimeWorked: (DateDiff("n",[StartTime],[EndTime])-[Lunch])\60 &
Format((DateDiff("n",[StartTime],[EndTime])-[Lunch]) Mod 60,"\:00")

Hope that helps

Craig

 

[Jaffa via AccessMonster.com]

Thank you Philip, Steve and Craig. I really appreciate you taking the time to
look at this for me and for your explanations.

I see that I have misunderstood how access calculates time and I'm still not
entirely sure, but it's got me thinking at least!

Craig, I did the following:

In the Hours worked field I inserted your expression

HrsWkd: (DateDiff("n",[MonS],[MonF])-[MonL])\60&Format((DateDiff("n",[MonS],
[MonF])-[MonL]) Mod 60,"\:00")

Data:
MonS: 8:30
MonF:17:00
MonL: 30
Result: -703:00

I'm doing something wrong, aren't I?

Well I think Philips answer won't work in a query.

And Steve just doesn't answer what you really want. He is also wrong about
the 8.3 it will enter as 8:03 and 8.30 will enter as 8:30 - try it and see.
He is right about your "Spreadsheet" aproach and that Is why I changed the
names.

But to get what you want and not how you said it - 7:45 showing hours and
minutes it is this

TimeWorked: (DateDiff("n",[StartTime],[EndTime])-[Lunch])\60 &
Format((DateDiff("n",[StartTime],[EndTime])-[Lunch]) Mod 60,"\:00")

Hope that helps

Craig

MonS - Monday Start Time (Date/Time)
MonF - Monday Finish Time (Date/Time)

[quoted text clipped - 10 lines]

Can someone please help me with this!
Thanks!

 

[Jaffa via AccessMonster.com]

I just re-read about the 'spreadsheet' approach...you guys are right...i know
it...

It's just that I need to be able to enter in data for 2 weeks - Monday to
Friday for each individual...so i needed different field names to identifiy
them..

For example for the second week I have name them MON2S, MON2F, MON2L etc....

Having used only Excel till now, I'm still in that mindset...is there a
better way...?

Thank you Philip, Steve and Craig. I really appreciate you taking the time to
look at this for me and for your explanations.

I see that I have misunderstood how access calculates time and I'm still not
entirely sure, but it's got me thinking at least!

Craig, I did the following:

In the Hours worked field I inserted your expression

HrsWkd: (DateDiff("n",[MonS],[MonF])-[MonL])\60&Format((DateDiff("n",[MonS],
[MonF])-[MonL]) Mod 60,"\:00")

Data:
MonS: 8:30
MonF:17:00
MonL: 30
Result: -703:00

I'm doing something wrong, aren't I?

Well I think Philips answer won't work in a query.

[quoted text clipped - 18 lines]

 

[John W. Vinson]

It's just that I need to be able to enter in data for 2 weeks - Monday to
Friday for each individual...so i needed different field names to identifiy
them..

For example for the second week I have name them MON2S, MON2F, MON2L etc....

Having used only Excel till now, I'm still in that mindset...is there a
better way...?

There is a better way.

"Fields are expensive, records are cheap". If you have ten days - or ten
thousand days! - of data to enter, each day's data should be entered *in a
separete record*.

Typically you could use a Form based on the people table, with a Subform based
on a second Hours table, with a PersonnelID field as a link to the people
table, and fields for the starting date/time and ending date/time. Each "bout"
of work would be stored as one record; if someone works eleven days during a
two week period, there'd be eleven records for that person in that date range.

 

[CraigH]

You probably have a date in the field instead of just a time and are
calculating it over multiple days.

Craig

Thank you Philip, Steve and Craig. I really appreciate you taking the time to
look at this for me and for your explanations.

I see that I have misunderstood how access calculates time and I'm still not
entirely sure, but it's got me thinking at least!

Craig, I did the following:

In the Hours worked field I inserted your expression

HrsWkd: (DateDiff("n",[MonS],[MonF])-[MonL])\60&Format((DateDiff("n",[MonS],
[MonF])-[MonL]) Mod 60,"\:00")

Data:
MonS: 8:30
MonF:17:00
MonL: 30
Result: -703:00

I'm doing something wrong, aren't I?

Well I think Philips answer won't work in a query.

And Steve just doesn't answer what you really want. He is also wrong about
the 8.3 it will enter as 8:03 and 8.30 will enter as 8:30 - try it and see.
He is right about your "Spreadsheet" aproach and that Is why I changed the
names.

But to get what you want and not how you said it - 7:45 showing hours and
minutes it is this

TimeWorked: (DateDiff("n",[StartTime],[EndTime])-[Lunch])\60 &
Format((DateDiff("n",[StartTime],[EndTime])-[Lunch]) Mod 60,"\:00")

Hope that helps

Craig

MonS - Monday Start Time (Date/Time)
MonF - Monday Finish Time (Date/Time)

[quoted text clipped - 10 lines]

Can someone please help me with this!
Thanks!

 

[Jaffa via AccessMonster.com]

I'm sorry, I didn't follow that...
In the Hours Worked table, the fields such as MonS, MonF, etc are Date/Time
format
I haven't set it to anything else in the query...

You probably have a date in the field instead of just a time and are
calculating it over multiple days.

Craig

Thank you Philip, Steve and Craig. I really appreciate you taking the time to
look at this for me and for your explanations.

[quoted text clipped - 39 lines]

 

[Philip Herlihy]

Well I think Philips answer won't work in a query.

Craig - see:

http://cid-2e572770a3a5cd55.skydrive.live.com/self.aspx/Public/Acc-Format-Time-query.jpg

Phil

 

[Philip Herlihy]

I'm sorry, I didn't follow that...
In the Hours Worked table, the fields such as MonS, MonF, etc are Date/Time
format
I haven't set it to anything else in the query...

You probably have a date in the field instead of just a time and are
calculating it over multiple days.

Craig

Thank you Philip, Steve and Craig. I really appreciate you taking the time to
look at this for me and for your explanations.

[quoted text clipped - 39 lines]

Can someone please help me with this!
Thanks!

A bit more explanation is needed here. Access stores Date/Time fields
as a "real" number, so it has an integral part and a fractional part.
The integral part represents the day, and the fraction represents the
time. (I don't believe it's easy to see this directly, as Access
protects the integrity of the values, making sure you can't have 30th
Feb, for example). You have to use the Date/Time functions (see the
Expression Builder, or Help) to manipulate these values.

When you enter a value into a Date/Time field, Access converts what you
enter into its Date/Time format, using separators (like / - to
"parse" the value to obtain a valid date number.

Once you have a valid date you can display it in various ways. If you
are only interested in Times, you can set the format to one of the Time
options and you're effectively hiding the date part of the value. The
obverse is true if you format only as a date - you're hiding the Time
(fraction) part.

If you perform arithmetic on dates, you can end up with a general number
(long, which is a real number) - this is what you need if you're going
to calculate payroll, for example. You can display this number in
various ways: as a plain number, or you can use the "format" function to
choose how to display it (as a string). I could only find the full list
of "custom format" characters in the VB Help, under "Format Property -
Date/Time Data Type" (Acc 2003).

Incidentally, I didn't pick up my mistake about 7.75 being equivalent to
7:45 - it isn't, as you'll see from the above. Sorry about that - it
just goes to show how smart the real experts are, because they don't
make this sort of mistake!

Try typing this into an empty "Field" box in the query builder:
Tea-Time: =format(0.75, "hh:mm").
You should see the value "18:00" when you run it, which is
three-quarters through the day. You get the same output if you change
the 0.75 to 99.75 or any other value ending .75.

Does that get you where you need to be?

Phil

 

[CraigH]

Basically Jaffa you probably have a the date in the field in the Table:

MonS 2/1/2009 8:30 AM
MonF 1/3/2009 5:00 PM

Because you got a negative number the start date is after the end date.
Although I couldn't duplicate the -703 closest I got was -700 so there may
be something else.

First make sure of what is in you table for the Dates/time - change the
format so you see both date and time.

If this is not it I don't have any other ideas without looking at it myself.

Craig

I'm sorry, I didn't follow that...
In the Hours Worked table, the fields such as MonS, MonF, etc are Date/Time
format
I haven't set it to anything else in the query...

You probably have a date in the field instead of just a time and are
calculating it over multiple days.

Craig

Thank you Philip, Steve and Craig. I really appreciate you taking the time to
look at this for me and for your explanations.

[quoted text clipped - 39 lines]

Can someone please help me with this!
Thanks!

 

[Jaffa via AccessMonster.com]

Phil & Craig,

thank you so much for your assistance....it was more than I expected and so
thorough!!! With your expert knowledge I have been able to get the desired
result of it displaying 7:45 to mean 7 hours and 45 minutes. Couldn't have
done it with you guys!

I have another query...i've inserted the following sql code for each day of
the week for 2 weeks (i've only inserted a couple of lines here) - but it
doesn't seem to compile...could you pls take a look if its not too much to
ask...

SELECT Pay.[Last name], Pay.[Known as], Pay.FTE, Pay.[Organisational Unit
Text], WorkHours.Thu2S, WorkHours.Thu2F, WorkHours.Thu2L, (DateDiff("n",
[Thu2S],[Thu2F])-[Thu2L])\60 & Format((DateDiff("n",[Thu2S],[Thu2F])-[Thu2L])
Mod 60,"\:00") AS ThuHours,

SELECT Pay.[Last name], Pay.[Known as], Pay.FTE, Pay.[Organisational Unit
Text], WorkHours.Fri2S, WorkHours.Fri2F, WorkHours.Fri2L, (DateDiff("n",
[Fri2S],[Fri2F])-[Fri2L])\60 & Format((DateDiff("n",[Fri2S],[Fri2F])-[Fri2L])
Mod 60,"\:00") AS FriHours,

FROM WorkHours INNER JOIN Pay ON WorkHours.[Personnel Number] = Pay.
[Personnel Number];

 

[Philip Herlihy]

Phil & Craig,

thank you so much for your assistance....it was more than I expected and so
thorough!!! With your expert knowledge I have been able to get the desired
result of it displaying 7:45 to mean 7 hours and 45 minutes. Couldn't have
done it with you guys!

I have another query...i've inserted the following sql code for each day of
the week for 2 weeks (i've only inserted a couple of lines here) - but it
doesn't seem to compile...could you pls take a look if its not too much to
ask...

SELECT Pay.[Last name], Pay.[Known as], Pay.FTE, Pay.[Organisational Unit
Text], WorkHours.Thu2S, WorkHours.Thu2F, WorkHours.Thu2L, (DateDiff("n",
[Thu2S],[Thu2F])-[Thu2L])\60 & Format((DateDiff("n",[Thu2S],[Thu2F])-[Thu2L])
Mod 60,"\:00") AS ThuHours,

SELECT Pay.[Last name], Pay.[Known as], Pay.FTE, Pay.[Organisational Unit
Text], WorkHours.Fri2S, WorkHours.Fri2F, WorkHours.Fri2L, (DateDiff("n",
[Fri2S],[Fri2F])-[Fri2L])\60 & Format((DateDiff("n",[Fri2S],[Fri2F])-[Fri2L])
Mod 60,"\:00") AS FriHours,

FROM WorkHours INNER JOIN Pay ON WorkHours.[Personnel Number] = Pay.
[Personnel Number];

Craig - see:

http://cid-2e572770a3a5cd55.skydrive.live.com/self.aspx/Public/Acc-Format-Time-query.jpg

Phil

This is hard, given that we don't have the database in front of us. A
few comments:

If you have to write code for each day of the week, your table design is
wrong - there has to be a better way of doing this!

In the fragment you've posted, you have two SELECT keywords but only one
FROM - that can't be right.

You'd want to delete the comma before the FROM.

One of your query output fields appears to be a concatenation (&) of the
result of the DateDiff function and the Formatted result of the DateDiff
function. What sort of thing is that?

I don't know what Fri2F (e.g.) is, but you're using DateDiff so they are
probably date/time values and you're simply trying to subtract one from
the other (e.g. [Fri2F])-[Fri2L]) within the DateDiff function call.
Use the functions provided to manipulate dates!

The way to build up a complex query like this is to get it working in
stages, adding a term when everything else is ok - that way you know
where the problem is. Learn to use the Expression Builder - richly
repays study. However, if you don't have your table design right,
everything which follows will be orders of magnitude more complex and
you'll have a struggle on your hands. I think you need to devise a more
general way of describing these time periods, so that the
day-of-the-week is a value, not a field.

Phil (interested amateur only - look out for the experts!)

 

[Steve Sanford]

repays study. However, if you don't have your table design right,

Here is a link to an Access 2007 template named "Time card". (Wish there
were examples for A2k -A2K3)

http://office.microsoft.com/en-us/templates/TC102220941033.aspx?CategoryID=CT101428241033


I don't have A2K7, so I don't know what the table structure looks like. But
maybe it will give you some ideas on how to fix your design.



HTH