Excel - calculating if a specific time is AM or PMExcel: Calculating working hours between two datesWord...
It took me a lot of time to make this, pls like. (YouTube Comments #1)
What can I substitute for soda pop in a sweet pork recipe?
Sometimes a banana is just a banana
How to count occurrences of Friday 13th
Called into a meeting and told we are being made redundant (laid off) and "not to share outside". Can I tell my partner?
Understanding Kramnik's play in game 1 of Candidates 2018
Why is working on the same position for more than 15 years not a red flag?
Can you use a beast's innate abilities while polymorphed?
Why proton concentration is divided by 10⁻⁷?
Contradiction with Banach Fixed Point Theorem
What are these green text/line displays shown during the livestream of Crew Dragon's approach to dock with the ISS?
As a new poet, where can I find help from a professional to judge my work?
Did 5.25" floppies undergo a change in magnetic coating?
Whom do I have to contact for a ticket refund in case of denied boarding (in the EU)?
How to mitigate "bandwagon attacking" from players?
What is this waxed root vegetable?
What is a term for a function that when called repeatedly, has the same effect as calling once?
Easy code troubleshooting in wordpress
Skis versus snow shoes - when to choose which for travelling the backcountry?
Has the Isbell–Freyd criterion ever been used to check that a category is concretisable?
Is there a German word for “analytics”?
How can I be pwned if I'm not registered on that site?
Can you 'upgrade' leather armor to studded leather armor without purchasing the new armor directly?
Must a tritone substitution use a dominant seventh chord?
Excel - calculating if a specific time is AM or PM
Excel: Calculating working hours between two datesWord table copy/drag formula like ExcelCalculating a duration in excel from date and timeExcel function for calculating total spent timeSpreadsheet for Daily Equipment HoursExcel Formula to Calculate Sum of Time Duration Between a Specific Time FrameExcel Formula for Sum of hours based on date selectedConverting time from text to hours and minutes in ExcelHow do you round a time and date back to a previous day with a formula in excel?Excel Formula : Find out time spent in specific time ranges
I'm trying to run a time-sheet import excel sheet to our software. It has columns as follows: 'employee name', 'start time', 'end time', and 'total hours'. Problem is that some 'start time' and 'end time' rows don't have AM/PM. When i edit the formatting of the cell it should show if it's AM or PM it just adds AM to all.
example: start time 8:00 end time 3:30 total hours 7.5
In the above example, the end time 3:30 is clearly PM since total hours is 7.5.
Question: is there a formula in excel to add in the start/end time cells whether this time is AM or PM ACCORDING TO THE TOTAL HOURS?
microsoft-excel
asked 7 hours ago
S MeiselsS Meisels
11
New contributor
S Meisels is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
I'm trying to run a time-sheet import excel sheet to our software. It has columns as follows: 'employee name', 'start time', 'end time', and 'total hours'. Problem is that some 'start time' and 'end time' rows don't have AM/PM. When i edit the formatting of the cell it should show if it's AM or PM it just adds AM to all.
example: start time 8:00 end time 3:30 total hours 7.5
In the above example, the end time 3:30 is clearly PM since total hours is 7.5.
Question: is there a formula in excel to add in the start/end time cells whether this time is AM or PM ACCORDING TO THE TOTAL HOURS?
microsoft-excel
asked 7 hours ago
S MeiselsS Meisels
11
New contributor
S Meisels is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1
If the times are all AM as formatting reveals, then when Start is AM and End should be PM (but is really stored as AM),End - Startshould either show no result or the wrong result. Perhaps the time worked is not calculated.
– Ted D.
6 hours ago
add a comment |
I'm trying to run a time-sheet import excel sheet to our software. It has columns as follows: 'employee name', 'start time', 'end time', and 'total hours'. Problem is that some 'start time' and 'end time' rows don't have AM/PM. When i edit the formatting of the cell it should show if it's AM or PM it just adds AM to all.
example: start time 8:00 end time 3:30 total hours 7.5
In the above example, the end time 3:30 is clearly PM since total hours is 7.5.
Question: is there a formula in excel to add in the start/end time cells whether this time is AM or PM ACCORDING TO THE TOTAL HOURS?
microsoft-excel
asked 7 hours ago
S MeiselsS Meisels
11
New contributor
S Meisels is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
I'm trying to run a time-sheet import excel sheet to our software. It has columns as follows: 'employee name', 'start time', 'end time', and 'total hours'. Problem is that some 'start time' and 'end time' rows don't have AM/PM. When i edit the formatting of the cell it should show if it's AM or PM it just adds AM to all.
example: start time 8:00 end time 3:30 total hours 7.5
In the above example, the end time 3:30 is clearly PM since total hours is 7.5.
Question: is there a formula in excel to add in the start/end time cells whether this time is AM or PM ACCORDING TO THE TOTAL HOURS?
microsoft-excel
microsoft-excel
asked 7 hours ago
S MeiselsS Meisels
11
New contributor
S Meisels is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 7 hours ago
S MeiselsS Meisels
11
New contributor
S Meisels is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 7 hours ago
S MeiselsS Meisels
11
New contributor
S Meisels is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 7 hours ago
S MeiselsS Meisels
11
asked 7 hours ago
S MeiselsS Meisels
11
11
New contributor
S Meisels is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
S Meisels is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
S Meisels is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1
If the times are all AM as formatting reveals, then when Start is AM and End should be PM (but is really stored as AM),End - Startshould either show no result or the wrong result. Perhaps the time worked is not calculated.
– Ted D.
6 hours ago
add a comment |
1
If the times are all AM as formatting reveals, then when Start is AM and End should be PM (but is really stored as AM),End - Startshould either show no result or the wrong result. Perhaps the time worked is not calculated.
– Ted D.
6 hours ago
1
1
If the times are all AM as formatting reveals, then when Start is AM and End should be PM (but is really stored as AM),
End - Start should either show no result or the wrong result. Perhaps the time worked is not calculated.– Ted D.
6 hours ago
If the times are all AM as formatting reveals, then when Start is AM and End should be PM (but is really stored as AM),
End - Start should either show no result or the wrong result. Perhaps the time worked is not calculated.– Ted D.
6 hours ago
add a comment |
2 Answers
2
active
oldest
votes
Select the following Time format.
The PM time format shows AM and PM
Notice the sample is showing AM for the data currently in the cell.
answered 7 hours ago
Ted D.Ted D.
4618
1
If making this change shows AM for all times, then End - Start should either show no result or the wrong result. Perhaps the time worked is not calculated?
– Ted D.
7 hours ago
add a comment |
Import: Use an Excel Sheet, Helper-Column Instead of End Time column.
End Time Helper-Column =[StartTime] + [TimeWork]
- Depending on the software doing the import, this may be a solution
available in the import script or the import configuration settings.
Further refinement when start times occur in the PM:
Start Time Helper-Column =If([StartTime] > [AM/PM-CutOff], [StartTime], [StartTime] + TIME(12,0,0))
- Where [AM/PM-CutOff] is
Time(Hours,Mins,Secs) - After what hour:Min is the Start time definitely AM? (Is 4:00 an AM or PM start time?)
- The cut off could be further refined based on the hours worked.
answered 6 hours ago
Ted D.Ted D.
4618
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
S Meisels is a new contributor. Be nice, and check out our Code of Conduct.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1411193%2fexcel-calculating-if-a-specific-time-is-am-or-pm%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
Select the following Time format.
The PM time format shows AM and PM
Notice the sample is showing AM for the data currently in the cell.
answered 7 hours ago
Ted D.Ted D.
4618
1
If making this change shows AM for all times, then End - Start should either show no result or the wrong result. Perhaps the time worked is not calculated?
– Ted D.
7 hours ago
add a comment |
Select the following Time format.
The PM time format shows AM and PM
Notice the sample is showing AM for the data currently in the cell.
answered 7 hours ago
Ted D.Ted D.
4618
1
If making this change shows AM for all times, then End - Start should either show no result or the wrong result. Perhaps the time worked is not calculated?
– Ted D.
7 hours ago
add a comment |
Select the following Time format.
The PM time format shows AM and PM
Notice the sample is showing AM for the data currently in the cell.
answered 7 hours ago
Ted D.Ted D.
4618
Select the following Time format.
The PM time format shows AM and PM
Notice the sample is showing AM for the data currently in the cell.
answered 7 hours ago
Ted D.Ted D.
4618
answered 7 hours ago
Ted D.Ted D.
4618
answered 7 hours ago
Ted D.Ted D.
4618
answered 7 hours ago
Ted D.Ted D.
4618
4618
1
If making this change shows AM for all times, then End - Start should either show no result or the wrong result. Perhaps the time worked is not calculated?
– Ted D.
7 hours ago
add a comment |
1
If making this change shows AM for all times, then End - Start should either show no result or the wrong result. Perhaps the time worked is not calculated?
– Ted D.
7 hours ago
1
1
If making this change shows AM for all times, then End - Start should either show no result or the wrong result. Perhaps the time worked is not calculated?
– Ted D.
7 hours ago
If making this change shows AM for all times, then End - Start should either show no result or the wrong result. Perhaps the time worked is not calculated?
– Ted D.
7 hours ago
add a comment |
Import: Use an Excel Sheet, Helper-Column Instead of End Time column.
End Time Helper-Column =[StartTime] + [TimeWork]
- Depending on the software doing the import, this may be a solution
available in the import script or the import configuration settings.
Further refinement when start times occur in the PM:
Start Time Helper-Column =If([StartTime] > [AM/PM-CutOff], [StartTime], [StartTime] + TIME(12,0,0))
- Where [AM/PM-CutOff] is
Time(Hours,Mins,Secs) - After what hour:Min is the Start time definitely AM? (Is 4:00 an AM or PM start time?)
- The cut off could be further refined based on the hours worked.
answered 6 hours ago
Ted D.Ted D.
4618
add a comment |
Import: Use an Excel Sheet, Helper-Column Instead of End Time column.
End Time Helper-Column =[StartTime] + [TimeWork]
- Depending on the software doing the import, this may be a solution
available in the import script or the import configuration settings.
Further refinement when start times occur in the PM:
Start Time Helper-Column =If([StartTime] > [AM/PM-CutOff], [StartTime], [StartTime] + TIME(12,0,0))
- Where [AM/PM-CutOff] is
Time(Hours,Mins,Secs) - After what hour:Min is the Start time definitely AM? (Is 4:00 an AM or PM start time?)
- The cut off could be further refined based on the hours worked.
answered 6 hours ago
Ted D.Ted D.
4618
add a comment |
Import: Use an Excel Sheet, Helper-Column Instead of End Time column.
End Time Helper-Column =[StartTime] + [TimeWork]
- Depending on the software doing the import, this may be a solution
available in the import script or the import configuration settings.
Further refinement when start times occur in the PM:
Start Time Helper-Column =If([StartTime] > [AM/PM-CutOff], [StartTime], [StartTime] + TIME(12,0,0))
- Where [AM/PM-CutOff] is
Time(Hours,Mins,Secs) - After what hour:Min is the Start time definitely AM? (Is 4:00 an AM or PM start time?)
- The cut off could be further refined based on the hours worked.
answered 6 hours ago
Ted D.Ted D.
4618
Import: Use an Excel Sheet, Helper-Column Instead of End Time column.
End Time Helper-Column =[StartTime] + [TimeWork]
- Depending on the software doing the import, this may be a solution
available in the import script or the import configuration settings.
Further refinement when start times occur in the PM:
Start Time Helper-Column =If([StartTime] > [AM/PM-CutOff], [StartTime], [StartTime] + TIME(12,0,0))
- Where [AM/PM-CutOff] is
Time(Hours,Mins,Secs) - After what hour:Min is the Start time definitely AM? (Is 4:00 an AM or PM start time?)
- The cut off could be further refined based on the hours worked.
answered 6 hours ago
Ted D.Ted D.
4618
answered 6 hours ago
Ted D.Ted D.
4618
answered 6 hours ago
Ted D.Ted D.
4618
answered 6 hours ago
Ted D.Ted D.
4618
4618
add a comment |
add a comment |
S Meisels is a new contributor. Be nice, and check out our Code of Conduct.
S Meisels is a new contributor. Be nice, and check out our Code of Conduct.
S Meisels is a new contributor. Be nice, and check out our Code of Conduct.
S Meisels is a new contributor. Be nice, and check out our Code of Conduct.
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1411193%2fexcel-calculating-if-a-specific-time-is-am-or-pm%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
If the times are all AM as formatting reveals, then when Start is AM and End should be PM (but is really stored as AM),
End - Startshould either show no result or the wrong result. Perhaps the time worked is not calculated.– Ted D.
6 hours ago