So I have the following Table
Columns
A | B | C | D
Line # | From Date | To Date | Days Worked
I want to create a formula to the side that allows me to sum the days worked between any 2 dates. I've been trying it with this with no luck. K8 is the start date I want to use for my query and L8 is the end date. This formula is to show total days worked.
=SUMIFS(D:D,C:C,">=K8",D:D,"<=L8")
Last edited by Custhasno; 01-14-2025 at 05:26 AM. Reason: attaching example file
How aboutFormula:
Please Login or Register to view this content.
No change, it is simply display 0 in the output, i have include a screen shot. Not sure if SUMIFS is even the correct way to go, but I need to be able to quickly check a range of date for total days worked
Attachment 886685
The attachment comes back as invalid, can you post a workbook that shows the problem.
Sorry had to finish up for yesterday, file attached now.
You have two "space" characters at the end of the dates in columns C and D, so these are forcing the dates to be text values rather than proper dates. One of the space characters is the "normal" space, with a code of 32, but the other one is the non-breaking space character which has a code of 160.
You can get rid of them in one operation by selecting those two columns and using Find & Replace (Ctrl-H):
Find What: Alt-0160space
Replace with: leave blank
Then click Replace All
where Alt-0160space means to hold down the Alt key and type 0160 on the numeric keypad and follow it with a normal space character. Alternatively, you could copy those two characters from the end of one of the dates and paste them in to that field.
You should get the result shown in cell M4 of 18 days.
Hope this helps.
Pete
Thanks, the actual data that gets imported is 01/03/1994 am. So i did the Find replace to remove the am and it wouldn't do it without a space being left. Ill use your advice to sort the issue at the source. Thanks
Are you still using Excel 2010? If not, please update your profile.
If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.
Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.
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.
If you copy/paste data in from the internet you often get the non-breaking space character between columns as html uses that character quite extensively to maintain the look of tabular data.
Anyway, if that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.
You might also like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).
Pete
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks