LinkBack URL
About LinkBacks
What I'm trying to do is, to make the time with the automatic time adjustment and the adding the number of hours of the week.
but I can't find the way for the adjustment of the time and for some reason adding the hour of week is not helping either
What I'm trying to do is, to make the time with the automatic time adjustment and the adding the number of hours of the week.
but I can't find the way for the adjustment of the time and for some reason adding the hour of week is not helping either
Change the format of the cells to Custom, [h]:mm
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Thanks for the reply, I changed the format of the cell; now I got the answer as 182:15 from 14:15 but the correct answer is 62:15Originally Posted by AlKey
Is there a way to get the time adjust (base on the chart) instead of entering manually.
Thanks in advance.
Hi Wolfin2010. I think this should do what you are asking.
At my work we use manual time cards. At the end of payroll, I have to input them into a spreadsheet
manually to reflect each time card. I think I may have the same issue you are having, but i still want the sheet
to reflect the actual time and still be able to make the adjustments to the "Nearest" 15 minutes when I total them up.
Here's what I came up with, Hope it helps!
It works for me, but if anyone has an easier way of writing the formula I used, Please let me know. I'm new to this as well.
Hi Wolfin2010,
I think that your formula to calculate hours maybe wrong. Here is formulas that will give you the desired results: 62:15
Try this
For Adjusted Start Time:
Enter in C2 and copy down
Formula:
=IF(B2="","",MROUND(B2,15/60/24))
For Adjusted Finish Time:
Enter in E2 and copy down
Formula:=IF(B2="","",MROUND(D2,15/60/24))
For Total Hours:
Formula:=IF(B2="","",E2-C2)
Format as Custom, hh:mm
For Total format Custom, [h]:mm
v A B C D E F G 1 Date Start Time Adujusted Time Finish Time Adujusted Time Total Hours TOTAL HOURS 2 ww 14-Aug-17 07:00 07:00 20:20 20:15 13:15 13:15 3 ww 15-Aug-17 08:11 08:15 20:11 20:15 12:00 12:00 4 ww 16-Aug-17 07:20 07:15 19:50 19:45 12:30 12:30 5 ww 17-Aug-17 08:12 08:15 19:38 19:45 11:30 11:30 6 ww 18-Aug-17 07:06 07:00 19:57 20:00 13:00 13:00 7 ww 19-Aug-17 8 ww 20-Aug-17 14:15 62:15
Here is another way: You can actually get rid of the adjusted columns and let the formula to make adjustments.
Enter formula in D2 and copy down
Formula:=IF(B2="","",MROUND(C2,"00:15")-MROUND(B2,"00:15"))
Format columns B and C as Custom, hh:mm
Format Total (cell D8) as Custom, [h]:mm
v A B C D 1 Date Start Time Finish Time TOTAL HOURS 2 ww 14-Aug-17 07:00 20:20 13:15 3 ww 15-Aug-17 08:11 20:11 12:00 4 ww 16-Aug-17 07:20 19:50 12:30 5 ww 17-Aug-17 08:12 19:38 11:30 6 ww 18-Aug-17 07:06 19:57 13:00 7 ww 19-Aug-17 8 ww 20-Aug-17 62:15 9
Last edited by AlKey; 09-18-2017 at 01:14 PM.
I'm trying to update my time sheet with the sales but I'm having little problem with the calculation.
Time1.png
J7 should says 1274.45 not 1642.45
I7 Format Cell: Custom
$ [h].mm
Last edited by Wolfin2010; 02-17-2019 at 12:11 PM.
What is the problem you are having? Where is J7? What is the formula?
Attach the workbook, not a picture of it.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
example file would be usefull. however an educated guess tells me that you are converting time 16 hours to decimal 16 twice..
the difference between 1258 and 1642 = 384 divide that by 24 and you get 16.00
So there is a problem with the formulas somewhere it says ??*24 where it should not..
I thought I posted the file as-well
Explain why.J7 should says 1274.45 not 1642.45
don't remember why, maybe I was using WPS before.
because of simple calculation 1258.45+16=1274.45 not 1642.45
You need to do this:
1. Cells G8 and those in I and J need to be set to ACCOUNTING formatting.
2. The formula in I7 needs to be =IF(F7="","",F7*J4*24)
Last edited by AliGW; 02-17-2019 at 01:31 PM.
Is this an Excel query? What does WPS Spreadsheets mean in your profile?
Please amend your location in your profile: helpers use this to determine your locale. which may affect solutions offered. A broad indication will do (e.g. UK, USA, etc,).
Last edited by AliGW; 02-17-2019 at 01:37 PM.
Please change your profile to show the version of Office you are using. Also change your location, as requested above.
Why are you using time formatting for a currency field? That's what's causing part of the problem.
Last edited by AliGW; 02-17-2019 at 01:38 PM.
Please note I've made a slight amendment to the formula (position of final bracket or brace). I have also updated the attachment,
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
So, did my suggestion help? You haven’t posted back or marked the thread as solved - do we still have an issue, or is it resolved? Some feedback one way or the other would be appreciated.
It would also be appreciated if you would make the amendments to your profile that I requested, thanks for your co-operation.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks