# Time sheet problems **URL:** \ **Category:** Ask for Help\ **Created:** [February 15, 2022, 11:39am UTC](https://community.glideapps.com/t/time-sheet-problems/38512 "2022-02-15T11:39:38Z")\ **Posts on this page:** 17\ **Page:** 1
**Author:** ![Sabiene](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/sabiene/32/38252_2.png) [@Sabiene](https://community.glideapps.com/u/Sabiene)\ **Post date:** [February 15, 2022, 11:39am UTC](https://community.glideapps.com/t/time-sheet-problems/38512/1 "2022-02-15T11:39:38Z")
Hi i’m new here. Trying for days to make my app work. It look so simpel in Excel but really need some help. I want to use a time picker for begin and end time. Also a time picker for a lunch Of course I want a column with ‘total worked hours’ and a column ‘without lunch’. Than the total worked hours must be rounded on 7:26 to 7:30 But I’m stuck. I’m struggling with time and decimal time. I don’t know why but when I use (eind - start)\*24 he also calculate lunch with it. ![Schermafbeelding 2022-02-15 om 12.36.14](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/6/7/67ad8aafd2e058fe65a6ea593b1cc724ce0034fb.png) Can anyone help me to make the right formula. For calculate time without lunch and to round the total time? ---
**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\ **Post date:** [February 15, 2022, 11:54am UTC](https://community.glideapps.com/t/time-sheet-problems/38512/2 "2022-02-15T11:54:07Z")
I think before we address the math formula problem, let’s talk about how we get the inputs. This is assuming you will get the inputs from Glide, not an external service. It must be noted that you won’t be able to catch only the hours & minutes of your start, end & lunch parts using a date/time picker. I think your start & end columns should be written to by two date/time pickers or an event picker, but it will go with the date alongside the time. Your lunch column can be written to by a stopwatch, or you can try an alternative by letting the users input the number of minutes they used to have lunch. ---
**Author:** ![Sabiene](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/sabiene/32/38252_2.png) [@Sabiene](https://community.glideapps.com/u/Sabiene)\ **Post date:** [February 15, 2022, 12:06pm UTC](https://community.glideapps.com/t/time-sheet-problems/38512/3 "2022-02-15T12:06:27Z")
OMG you are fast… I need to translate it… Yes: the input is from Glide with an event picker. “I think your start & end columns should be written to by two date/time pickers or an event picker, but it will go with the date alongside the time.” OK Then my preference would be to let the users enter the number of minutes they used to have lunch. ---
**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\ **Post date:** [February 15, 2022, 12:40pm UTC](https://community.glideapps.com/t/time-sheet-problems/38512/4 "2022-02-15T12:40:34Z")
To answer your original question, I formatted all my examples as I said above, timestamps for start & end, numbers representing minutes for lunch time. ![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/2/9/292214c7d59b4ba0994e441ab55d5cc8c8d72c84.png) The “without lunch” column calculates the total time without lunch, no rounding here. ![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/c/1/c16ad0460b7ce4f34b5e6e17e4cc839bcc70d5f7.png) ```auto E-S-L/24/60 ``` The “total time” column rounds up to the nearest 30 minute. ![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/4/9/49a0f4c02ba7f1cc74a1fb3ab19e58b59db522ef.png) ```auto WL-MOD(WL,1/48) +CEILING(ROUND(MOD(WL,1/48)*60*24)/30)*30/60/24 ``` ---
**Author:** ![Sabiene](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/sabiene/32/38252_2.png) [@Sabiene](https://community.glideapps.com/u/Sabiene)\ **Post date:** [February 15, 2022, 12:54pm UTC](https://community.glideapps.com/t/time-sheet-problems/38512/5 "2022-02-15T12:54:00Z")
I’m going to process it in my Glide. At least now I know where to look and that some things I had come up with are not possible. Thank you for your time and super quick response. Maybe I’ll eventually have a question with the totals per week. But will try that myself first. ---
**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\ **Post date:** [February 15, 2022, 1:01pm UTC](https://community.glideapps.com/t/time-sheet-problems/38512/6 "2022-02-15T13:01:37Z")
Don’t hesitate asking questions here, we’re always willing to help! ---
**Author:** ![Sabiene](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/sabiene/32/38252_2.png) [@Sabiene](https://community.glideapps.com/u/Sabiene)\ **Post date:** [February 15, 2022, 1:39pm UTC](https://community.glideapps.com/t/time-sheet-problems/38512/7 "2022-02-15T13:39:30Z")
> [@ThinhDinh](#): > > ```auto > WL-MOD(WL,1/48) > +CEILING(ROUND(MOD(WL,1/48)*60*24)/30)*30/60/24 > > ``` Can I disturb you one more time? Can I also round it up to the nearest quarter of an hour? 9:22 = 9:15 and 9:23 = 09:30 ---
**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\ **Post date:** [February 15, 2022, 3:32pm UTC](https://community.glideapps.com/t/time-sheet-problems/38512/8 "2022-02-15T15:32:07Z")
Just guessing here, but try changing 48 to 96. ---
**Author:** ![Sabiene](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/sabiene/32/38252_2.png) [@Sabiene](https://community.glideapps.com/u/Sabiene)\ **Post date:** [February 15, 2022, 3:43pm UTC](https://community.glideapps.com/t/time-sheet-problems/38512/9 "2022-02-15T15:43:50Z")
Thanks! unfortunately I already tried that. Quarter of an hour too far. 7:26 should be 7:30, 9:46 should be 9:45 ![Schermafbeelding 2022-02-15 om 16.40.46](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/b/7/b7b9c811213d3cee9b6eca8518beb09977e07ecb.png) ---
**Author:** ![jabid](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jabid/32/33391_2.png) [@jabid](https://community.glideapps.com/u/jabid)\ **Post date:** [February 15, 2022, 4:04pm UTC](https://community.glideapps.com/t/time-sheet-problems/38512/10 "2022-02-15T16:04:45Z")
I’m not in front of my computer but I think you should convert your date column to a number then set the corresponding number then put it back to date… or something like that… I seem to have done that before. ---
**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\ **Post date:** [February 15, 2022, 4:05pm UTC](https://community.glideapps.com/t/time-sheet-problems/38512/11 "2022-02-15T16:05:54Z")
OK. I’m guessing @ThinhDinh is probably sleeping now. I’m not by a computer right now to try it out either, but I’m sure one of us will get back to you when we have a chance. ---
**Author:** ![Sabiene](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/sabiene/32/38252_2.png) [@Sabiene](https://community.glideapps.com/u/Sabiene)\ **Post date:** [February 15, 2022, 4:08pm UTC](https://community.glideapps.com/t/time-sheet-problems/38512/12 "2022-02-15T16:08:00Z")
You are all great! As long as I know it’s possible I’ll keep trying. @jabid Thank you for your tip! Let me try that. But if you’re faster, I’d love to hear it. ---
**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\ **Post date:** [February 16, 2022, 12:15am UTC](https://community.glideapps.com/t/time-sheet-problems/38512/13 "2022-02-16T00:15:59Z")
![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/1/e/1e9a92caa4b764ec8cf9da8bd59146336fa3a298.png) ![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/5/2/52503f8a7cb338bd59c488605ded87a368612019.jpeg) Note: My 2nd example top-down is not the same as her since I wanted to trial a case that goes just above 30 mins (31). I haven’t had time to properly think about rounding to nearest, my formula only rounds up as of now. And you’re right about 1/96, it’s just that we need to replace 30 by 15 as well. ```auto WL-MOD(WL,1/96) +CEILING(ROUND(MOD(WL,1/96)*60*24)/15)*15/60/24 ``` ---
**Author:** ![Sabiene](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/sabiene/32/38252_2.png) [@Sabiene](https://community.glideapps.com/u/Sabiene)\ **Post date:** [February 16, 2022, 8:17am UTC](https://community.glideapps.com/t/time-sheet-problems/38512/14 "2022-02-16T08:17:07Z")
Yes, this does indeed look better. Thought I had already tried every possible way. But now there is indeed the possibility to round down. 09:46 \> 09:45 ![Schermafbeelding 2022-02-16 om 09.16.30](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/c/3/c37aa6a45b120c153dbdbf961a28bb8d5a0b2b32.png) ---
**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\ **Post date:** [February 16, 2022, 9:30am UTC](https://community.glideapps.com/t/time-sheet-problems/38512/15 "2022-02-16T09:30:09Z")
Apologies for me not thinking about this earlier, CEILING should have been replaced by ROUND and voila. ![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/1/5/15ddcda9346ed114ac573d875f574ee85b4b37bf.png) ![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/a/d/ade3a5610d80eafafafdd8f04451f5af5bab2ed3.png) ```auto WL-MOD(WL,1/96) +ROUND(ROUND(MOD(WL,1/96)*60*24)/15)*15/60/24 ``` ---
**Author:** ![Sabiene](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/sabiene/32/38252_2.png) [@Sabiene](https://community.glideapps.com/u/Sabiene)\ **Post date:** [February 16, 2022, 12:02pm UTC](https://community.glideapps.com/t/time-sheet-problems/38512/16 "2022-02-16T12:02:21Z")
> [@ThinhDinh](#): > > ```auto > WL-MOD(WL,1/96) > +ROUND(ROUND(MOD(WL,1/96)*60*24)/15)*15/60/24 > > ``` OMG this is so cool! This works. Thank you so much for your time and thinking along. ---
**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\ **Post date:** [February 16, 2022, 12:06pm UTC](https://community.glideapps.com/t/time-sheet-problems/38512/17 "2022-02-16T12:06:05Z")
My pleasure to help!