How to convert to 12 hours date format
- Thread starter m9vukyem
- Start date
The Main problem in the software once we have fixed the shift to particular employee wer cant change it if that person changed his duty timining without intimation to Biometric staff. so that i have to calcukate the late coming and early go in the excel file provided by software.
Peter Bartholomew
Well-Known Member
To calculate with time in Excel each time must be represented by a number between 0 and 1 (that is, a fraction of one day). So, although
"8am-4pm"
means something to you, Excel cannot perform calculations on the numbers (start and end of the shift) in that form. The start time might be provided by a formula such as
=SWITCH(Shift, "8am-4pm", 8/24, "2pm-8pm", 14/24, FALSE)
The clocking-in times are nearer to what is needed except they are text strings and not numbers. To convert you could use
= TIMEVALUE(TimeIn) or, more concisely, = --TimeIn
Your 15 minute period of grace could be represented by
= TIME(0,15,0) or you may prefer = 1/96
Only once such conversions have been performed, can excel provide information such as
"Was the employee late?" or
"How many hours did they work?"
by simply subtracting the relevant times.
"8am-4pm"
means something to you, Excel cannot perform calculations on the numbers (start and end of the shift) in that form. The start time might be provided by a formula such as
=SWITCH(Shift, "8am-4pm", 8/24, "2pm-8pm", 14/24, FALSE)
The clocking-in times are nearer to what is needed except they are text strings and not numbers. To convert you could use
= TIMEVALUE(TimeIn) or, more concisely, = --TimeIn
Your 15 minute period of grace could be represented by
= TIME(0,15,0) or you may prefer = 1/96
Only once such conversions have been performed, can excel provide information such as
"Was the employee late?" or
"How many hours did they work?"
by simply subtracting the relevant times.
Peter Bartholomew
Well-Known Member
What version of Excel is that?
Using a nested IF
= IF(Shift="8am-4pm", 8/24, IF(Shift="2pm-8pm", 14/24))
Peter Bartholomew
Well-Known Member
Bear in mind that I only reference data by name and my Excel defaults to array formulas. The array formulas are not needed for these calculations, they just simplify the solution for me.
Dear vletm &
Peter Bartholomew
sir
first i am apploging sir because i have lack of english knowledge i am unable sometimes to respond what you have replied to my posts. but thanks this site i am developing my excel skills using this site
Thankyou sir you are noticed issue is the Main Problem sirhow to calculate if anyone perfomed both shifts suppose 8am to 4 pm and night duty is 8pm to 8am they marking both times (in and out )
we are facing two issues in my biometric system 1st if anyone perfomed 2 shifts in a day it is not giving correct results and the second issue if he changed the duty without intimation to biometric staff it will give wrong result
Peter Bartholomew
sir
first i am apploging sir because i have lack of english knowledge i am unable sometimes to respond what you have replied to my posts. but thanks this site i am developing my excel skills using this site
Thankyou sir you are noticed issue is the Main Problem sirhow to calculate if anyone perfomed both shifts suppose 8am to 4 pm and night duty is 8pm to 8am they marking both times (in and out )
we are facing two issues in my biometric system 1st if anyone perfomed 2 shifts in a day it is not giving correct results and the second issue if he changed the duty without intimation to biometric staff it will give wrong result
m9vukyem
After Your above writing ...
Your file has four kind of shifts: 8am-4pm, 8am-8pm, 9am-8pm and 2pm-8pm - what is Your named 8pm-8am?
So far, You have been interesting differences from 8 workinghours ...
As I wrote, I didn't start to solve those double shifts ... it won't be a challenge after...
You could show Your expected results to Your sample data.
Before that - I can only guess ... guess
Do You have any idea, how to handle that Your 2nd issue?
Do that data have sample about that? If not ... then it would be challenge to even offer something.
Basic question: How would Your ... biometric system ... take care missed timestamps?
eg if as usually has happened: IN 08:00 ... missing OUT
if next day ... missing IN ... but finally stamps OUT at 16:00
Of course, everyone should stamp IN and OUT, but that has be 'normal' with many this kind of threads.
After Your above writing ...
Your file has four kind of shifts: 8am-4pm, 8am-8pm, 9am-8pm and 2pm-8pm - what is Your named 8pm-8am?
So far, You have been interesting differences from 8 workinghours ...
As I wrote, I didn't start to solve those double shifts ... it won't be a challenge after...
You could show Your expected results to Your sample data.
Before that - I can only guess ... guess
Do You have any idea, how to handle that Your 2nd issue?
Do that data have sample about that? If not ... then it would be challenge to even offer something.
Basic question: How would Your ... biometric system ... take care missed timestamps?
eg if as usually has happened: IN 08:00 ... missing OUT
if next day ... missing IN ... but finally stamps OUT at 16:00
Of course, everyone should stamp IN and OUT, but that has be 'normal' with many this kind of threads.
We have two types of Duties for staff 1) General 2) Shift
General consists 3 types of Duties Morning Duty 8AM TO 4PM OR 9AM TO 5 PM AND Night Duty Time Is today (16-12-2020)8pm to tommorrow(17-12-2020) Morning 8am after completion of night duty they will perform 4 hours duty on 17-12-2020 from Evening 4pm to Night 8 pm
General staff goes one hour early on the night duty day they will do morning duty as well as night duty on the same day the next day will do 4 hours evening duty.
General duty staff some members( long to stay to our office) evening duty perfomed before completion of their Night duty Exemple their night duty 19-12-2020 but their evening duty changed to someone and perfomed 14-12-2020 someone will come to 20-12-2020 for evening duty
shift Duty Timinings 8am to 2pm , 2pm to 8 pm, and 8pm to 8am
but in shift duty only morning duty and after noon duty will be clubbed or after noon duty will be clubbed to night Duty if we need
(i.e. if an employee not to come to duty for relieving Moning duty staff or After noon duty staff contine both duties) they will mark both time (2 shifts) in time and out time some of the employees are marking only once (i.e one in time and out time)
General consists 3 types of Duties Morning Duty 8AM TO 4PM OR 9AM TO 5 PM AND Night Duty Time Is today (16-12-2020)8pm to tommorrow(17-12-2020) Morning 8am after completion of night duty they will perform 4 hours duty on 17-12-2020 from Evening 4pm to Night 8 pm
General staff goes one hour early on the night duty day they will do morning duty as well as night duty on the same day the next day will do 4 hours evening duty.
General duty staff some members( long to stay to our office) evening duty perfomed before completion of their Night duty Exemple their night duty 19-12-2020 but their evening duty changed to someone and perfomed 14-12-2020 someone will come to 20-12-2020 for evening duty
shift Duty Timinings 8am to 2pm , 2pm to 8 pm, and 8pm to 8am
but in shift duty only morning duty and after noon duty will be clubbed or after noon duty will be clubbed to night Duty if we need
(i.e. if an employee not to come to duty for relieving Moning duty staff or After noon duty staff contine both duties) they will mark both time (2 shifts) in time and out time some of the employees are marking only once (i.e one in time and out time)
Last edited:
m9vukyem
Your data has used those four different shifts.
>> You seems to give some new not-used-shifts with You sample file. It's challenge for 'outsiders', if You cannot give exact information. <<
Is it matter for solution, which shift someone would use?
Please, try to focus to get expected results based Your sample data.
While waiting those and other needed details,
I tried to solve all workinghours, including if there is more than one shift.
Your data has used those four different shifts.
>> You seems to give some new not-used-shifts with You sample file. It's challenge for 'outsiders', if You cannot give exact information. <<
Is it matter for solution, which shift someone would use?
Please, try to focus to get expected results based Your sample data.
While waiting those and other needed details,
I tried to solve all workinghours, including if there is more than one shift.
Last edited:
m9vukyem
Where did You write expected results?
Did You even watch my previous files (#17) Whrs?
if an employee double duty in a day 1st duty timing is maximum hous in a shift minus 1 hour
... minus 1 hours ... what?
Is a double duty in a day different totally different than two shifts?
(i.e. if an employee not to come to duty for relieving Moning duty staff or After noon duty staff contine both duties) they will mark both time (2 shifts) in time and out time some of the employees are marking only once (i.e one in time and out time)
Maximum wkg hours in this shift only 6 hous
... means that ... all minutes after 6hrs do not count!
... and same with any shifts --- 8hrs shift means max 8hrs --- 12hrs shift means max 12hrs
... and of course, daily working hours calculates to each day
= if 'night shift' then one shift will split with two dates as in my previous file.
There seems to be 'some' more details which You needs to open ...
Where did You write expected results?
Did You even watch my previous files (#17) Whrs?
if an employee double duty in a day 1st duty timing is maximum hous in a shift minus 1 hour
... minus 1 hours ... what?
Is a double duty in a day different totally different than two shifts?
(i.e. if an employee not to come to duty for relieving Moning duty staff or After noon duty staff contine both duties) they will mark both time (2 shifts) in time and out time some of the employees are marking only once (i.e one in time and out time)
Maximum wkg hours in this shift only 6 hous
... means that ... all minutes after 6hrs do not count!
... and same with any shifts --- 8hrs shift means max 8hrs --- 12hrs shift means max 12hrs
... and of course, daily working hours calculates to each day
= if 'night shift' then one shift will split with two dates as in my previous file.
There seems to be 'some' more details which You needs to open ...
m9vukyem
1) My previous version did not have any formulas (#17 reply).
... and as I have tried to explain ... my previous versions calculations are different than my formula-results.
... I have asked to get Your expected correct verified results - no matter how have You solved those.
2) I did not get any answers to my previous questions.
3) Suggest You - give answers
1) My previous version did not have any formulas (#17 reply).
... and as I have tried to explain ... my previous versions calculations are different than my formula-results.
... I have asked to get Your expected correct verified results - no matter how have You solved those.
2) I did not get any answers to my previous questions.
3) Suggest You - give answers
Is a double duty in a day different totally different than two shifts
Reply: We have given another shift timing by manually in the excel file. my sytem does not allow to take 2 shifts in a day.
already i have sent the file in previous reply once you check and already marked expected results that are correct sir.
and i need to calculate -hrs & +hrs timinings as per shift timinings.
sorry for trouble sir.
Reply: We have given another shift timing by manually in the excel file. my sytem does not allow to take 2 shifts in a day.
already i have sent the file in previous reply once you check and already marked expected results that are correct sir.
and i need to calculate -hrs & +hrs timinings as per shift timinings.
sorry for trouble sir.
R
Rickbale
Guest
If you just want to enter Time into excel by 12-hour clock or 24-hour clock format, you can preset your cell format to a proper one before entering the time.
Step1: Open a blank excel, select column A for example, right click, select ‘Format Cells…’.
Step 2: In Format Cells, under Number tab, select Time in Category list. Then all Types are loaded in the right.
Step 3: You can select the Type you expect to show the time when you entering time data into excel. For example, we select the first one 12-hour format to show the time, click OK on Format Cells to save the preset.
Hope this helps
B
benmartin9920
Guest
If you just want to enter Time into excel by 12-hour clock or 24-hour clock format, you can preset your cell format to a proper one before entering the time.
Step1: Open a blank excel, select column A for example, right click, select ‘Format Cells
Step 2: In Format Cells, under Number tab, select Time in Category list. Then all Types are loaded in the right.
Step 3: You can select the Type you expect to show the time when you entering time data into excel. For example, we select the first one 12-hour format to show the time, click OK on Format Cells to save the preset.
I hope this information will be helpful in converting your excel data in 12 hour format.
Ben Martin