If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Time Sheet Calcuations
I am trying to design a time sheet to calculate automatically the time
entered for all shifts. This includes graveyard. Unfortunately I cannot seem to put together the right formula to calculate all time sets. Can anyone help me? Example: Time In Time Out I get Time Should be 18:00 03:00 15:00 9:00 Thanks for any help you can give. -- ELL |
#2
|
|||
|
|||
You can solve the problem best by including the date as well as the time in
the field. If you do that: DateDiff("n", [Time In], [Time Out]) will give you the correct number of minutes, even if it spans midnight. If that is not suitable and you are absolutely certain that there will ever be a graveyard shift of 12 hours or more duration, you could use: Minutes: DateDiff("n", [Time In], IIf([Time In] [Time Out], CDate(1+[Time Out]), [Time Out])) These calculations give you the duration in minutes, so you can perform numerical function such as sum, average, or calculate wages. If you wish to display the result on a report in minutes and hours, the Control Source for the text box would be: =[Minutes] \ 60 & Format([Minutes] Mod 60, "\:00") -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "ELL" wrote in message ... I am trying to design a time sheet to calculate automatically the time entered for all shifts. This includes graveyard. Unfortunately I cannot seem to put together the right formula to calculate all time sets. Can anyone help me? Example: Time In Time Out I get Time Should be 18:00 03:00 15:00 9:00 Thanks for any help you can give. -- ELL |
#3
|
|||
|
|||
You can solve the problem best by including the date as well as the time in
the field. If you do that: DateDiff("n", [Time In], [Time Out]) will give you the correct number of minutes, even if it spans midnight. If that is not suitable and you are absolutely certain that there will ever be a graveyard shift of 12 hours or more duration, you could use: Minutes: DateDiff("n", [Time In], IIf([Time In] [Time Out], CDate(1+[Time Out]), [Time Out])) These calculations give you the duration in minutes, so you can perform numerical function such as sum, average, or calculate wages. If you wish to display the result on a report in minutes and hours, the Control Source for the text box would be: =[Minutes] \ 60 & Format([Minutes] Mod 60, "\:00") -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "ELL" wrote in message ... I am trying to design a time sheet to calculate automatically the time entered for all shifts. This includes graveyard. Unfortunately I cannot seem to put together the right formula to calculate all time sets. Can anyone help me? Example: Time In Time Out I get Time Should be 18:00 03:00 15:00 9:00 Thanks for any help you can give. -- ELL |
#4
|
|||
|
|||
You can solve the problem best by including the date as well as the time in
the field. If you do that: DateDiff("n", [Time In], [Time Out]) will give you the correct number of minutes, even if it spans midnight. If that is not suitable and you are absolutely certain that there will ever be a graveyard shift of 12 hours or more duration, you could use: Minutes: DateDiff("n", [Time In], IIf([Time In] [Time Out], CDate(1+[Time Out]), [Time Out])) These calculations give you the duration in minutes, so you can perform numerical function such as sum, average, or calculate wages. If you wish to display the result on a report in minutes and hours, the Control Source for the text box would be: =[Minutes] \ 60 & Format([Minutes] Mod 60, "\:00") -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "ELL" wrote in message ... I am trying to design a time sheet to calculate automatically the time entered for all shifts. This includes graveyard. Unfortunately I cannot seem to put together the right formula to calculate all time sets. Can anyone help me? Example: Time In Time Out I get Time Should be 18:00 03:00 15:00 9:00 Thanks for any help you can give. -- ELL |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Time Sheet Calculations | Laura V | General Discussion | 4 | September 16th, 2004 12:50 PM |
Use first record found in expression? | CASJAS | Running & Setting Up Queries | 17 | July 22nd, 2004 09:21 PM |
Time Sheet function | John Logan | Worksheet Functions | 3 | October 7th, 2003 07:26 AM |